Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two tables and would like to join on two columns with equality and other using between. similar to the one in SQL as below. (excuse me for any syntax errors). I tried with IntervalMatch which does not seems to work because of extra check required for ID column.
INSERT INTO FINAL_TABLE (FromDate, ToDate, ID, Value, Type)
SELECT FromDate, ToDate, ID, Value, Type
FROM TABLE_A
UNION ALL
SELECT TABLE_B.FromDate, NULL as ToDate, TABLE_B.ID, TABLE_A.Value, TABLE_B.Type
FROM TABLE_B
LEFT JOIN TABLE_A ON TABLE_A.ID = TABLE_B.ID AND TABLE_B.FromDate BETWEEN TABLE_A.FromDate AND TABLE_A.ToDate
Table structures as following:
Table_A:
FromDate, ToDate, ID, Value, Type
01/01/2015, 01/30/2015, 1, 10, TableA
01/01/2015, 01/30/2015, 2, 11, TableA
02/01/2015, 02/15/2015, 2, 15, TableA
Table_B:
FromDate, ID, Type
01/15/2015, 1, TableB
01/15/2015, 2, TableB
02/15/2015, 2, TableB
02/15/2015, 3, TableB
Table to be created
Final_Table:
FromDate, ToDate, ID, Value, Type
01/01/2015, 01/30/2015, 1, 10, TableA
01/01/2015, 01/30/2015, 2, 11, TableA
02/01/2015, 02/15/2015, 2, 15, TableA
01/15/2015, NULL, 1, 10, TableB
01/15/2015, NULL, 2, 11, TableB
02/15/2015, NULL, 2, 15, TableB
02/15/2015, NULL, 3, NULL, TableB
This:
Script:
Table_A:
LOAD * Inline [
FromDate, ToDate, ID, Value, Type
01/01/2015, 01/30/2015, 1, 10, TableA
01/01/2015, 01/30/2015, 2, 11, TableA
02/01/2015, 02/15/2015, 2, 15, TableA
];
Table_B:
LOAD FromDate as Date,
ID,
Type as Type2
Inline [
FromDate, ID, Type
01/15/2015, 1, TableB
01/15/2015, 2, TableB
02/15/2015, 2, TableB
02/15/2015, 3, TableB
];
Join(Table_B)
IntervalMatch:
IntervalMatch (Date, ID)
LOAD FromDate,
ToDate,
ID
Resident Table_A;
NewTable:
NoConcatenate
LOAD FromDate,
ToDate,
ID,
Value
Resident Table_A;
Join(NewTable)
LOAD FromDate,
ToDate,
ID,
Date,
Type2
Resident Table_B;
Concatenate(Table_A)
LOAD Date as FromDate,
Null() as ToDate,
ID,
Type2 as Type,
Value
Resident NewTable;
DROP Tables NewTable, Table_B;
Table_A:
FromDate, ToDate, ID, Value, Type
01/01/2015, 01/30/2015, 1, 10, TableA
01/01/2015, 01/30/2015, 2, 11, TableA
02/01/2015, 02/15/2015, 2, 15, TableA
Left join(Table_A)
FromDate, ID, Type
01/15/2015, 1, TableB
01/15/2015, 2, TableB
02/15/2015, 2, TableB
02/15/2015, 3, TableB
Based on FromDate both the tables will be joined
Please observed the date values in the FromDate in TABLE_B they are not same as TABLE_A or in other words it is to be compared between TABLE_A FROM/TO Dates to find pick appropriate TABLE_A.Value.
This:
Script:
Table_A:
LOAD * Inline [
FromDate, ToDate, ID, Value, Type
01/01/2015, 01/30/2015, 1, 10, TableA
01/01/2015, 01/30/2015, 2, 11, TableA
02/01/2015, 02/15/2015, 2, 15, TableA
];
Table_B:
LOAD FromDate as Date,
ID,
Type as Type2
Inline [
FromDate, ID, Type
01/15/2015, 1, TableB
01/15/2015, 2, TableB
02/15/2015, 2, TableB
02/15/2015, 3, TableB
];
Join(Table_B)
IntervalMatch:
IntervalMatch (Date, ID)
LOAD FromDate,
ToDate,
ID
Resident Table_A;
NewTable:
NoConcatenate
LOAD FromDate,
ToDate,
ID,
Value
Resident Table_A;
Join(NewTable)
LOAD FromDate,
ToDate,
ID,
Date,
Type2
Resident Table_B;
Concatenate(Table_A)
LOAD Date as FromDate,
Null() as ToDate,
ID,
Type2 as Type,
Value
Resident NewTable;
DROP Tables NewTable, Table_B;
Sunny that was excellent,That was exactly what i wanted. I was not aware IntervalMatch() would take multiple parameters.
It does . I recently figured that out also.
I am glad we are learning something new each day.
Best,
Sunny