Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have one data to find the Interval match : Can anyone help in this regard?
Item | Odate | Crny | Cost |
1 | 15/1/2012 | EUR | 14 |
2 | 15/2/2012 | USD | 15 |
3 | 15/3/2012 | EUR | 16 |
4 | 15/8/2012 | USD | 10 |
Another data
Crny | From | To | rent |
USD | 1/1/2012 | 30/4/2012 | 1.25 |
USD | 1/6/2012 | 30/12/2012 | 1.29 |
EUR | 1/1/2012 | 31/1/2012 | 1.23 |
EUR | 2/2/2012 | 30/4/2012 | 2.25 |
The result should be
Item | ODate | Crny | From | To | Cost | rent |
1 | 1/15/2012 | EUR | 1/1/2012 | 1/31/2012 | 14 | 1.23 |
2 | 2/15/2012 | USD | 1/1/2012 | 4/30/2012 | 15 | 1.25 |
3 | 3/15/2012 | EUR | 2/2/2012 | 4/30/2012 | 16 | 2.25 |
4 | 8/15/2012 | USD | 6/1/2012 | 12/30/2012 | 10 | 1.29 |
Try this
Table1:
LOAD * INLINE [
Item, Odate, Crny, Cost
1, 15/1/2012, EUR, 14
2, 15/2/2012, USD, 15
3, 15/3/2012, EUR, 16
4, 15/8/2012, USD, 10
];
Table2:
LOAD * INLINE [
Crny, From, To, rent
USD, 1/1/2012, 30/4/2012, 1.25
USD, 1/6/2012, 30/12/2012, 1.29
EUR, 1/1/2012, 31/1/2012, 1.23
EUR, 2/2/2012, 30/4/2012, 2.25
];
Left Join (Table1)
IntervalMatch(Odate, Crny)
LOAD From,
To,
Crny
Resident Table2;
Left Join (Table1)
LOAD *
Resident Table2;
DROP Table Table2;
Try this
Table1:
LOAD * INLINE [
Item, Odate, Crny, Cost
1, 15/1/2012, EUR, 14
2, 15/2/2012, USD, 15
3, 15/3/2012, EUR, 16
4, 15/8/2012, USD, 10
];
Table2:
LOAD * INLINE [
Crny, From, To, rent
USD, 1/1/2012, 30/4/2012, 1.25
USD, 1/6/2012, 30/12/2012, 1.29
EUR, 1/1/2012, 31/1/2012, 1.23
EUR, 2/2/2012, 30/4/2012, 2.25
];
Left Join (Table1)
IntervalMatch(Odate, Crny)
LOAD From,
To,
Crny
Resident Table2;
Left Join (Table1)
LOAD *
Resident Table2;
DROP Table Table2;