Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys:
I want to join two tables where the 2nd has multiple entries.
Logic:
Match column A
if B within D,E display result
any help would be great, thank you!!!
You can use the IntervalMatch function for that:
Table1:
LOAD * INLINE [
A, B, C
1, 01/03/2016, 1.99
2, 01/27/2016, 2.99
3, 02/01/2016, 3.99
];
Table2_Temp:
LOAD * INLINE [
A, result, D, E
1, F, 01/01/2016, 01/04/2016
1, G, 01/06/2016, 01/09/2016
1, H, 01/11/2016, 01/14/2016
2, F, 01/16/2016, 01/19/2016
2, G, 01/21/2016, 01/24/2016
2, H, 01/26/2016, 01/29/2016
3, F, 01/31/2016, 02/03/2016
];
Table2:
IntervalMatch(B, A)
LOAD D, E, A
RESIDENT Table2_Temp;
LEFT JOIN (Table2)
LOAD *
RESIDENT Table2_Temp;
DROP TABLE Table2_Temp;
Note: The IntervalMatch function does create a synthetic key in order to link the one date between the other two. This synthetic key is fine and can be kept in your data model.
You can use the IntervalMatch function for that:
Table1:
LOAD * INLINE [
A, B, C
1, 01/03/2016, 1.99
2, 01/27/2016, 2.99
3, 02/01/2016, 3.99
];
Table2_Temp:
LOAD * INLINE [
A, result, D, E
1, F, 01/01/2016, 01/04/2016
1, G, 01/06/2016, 01/09/2016
1, H, 01/11/2016, 01/14/2016
2, F, 01/16/2016, 01/19/2016
2, G, 01/21/2016, 01/24/2016
2, H, 01/26/2016, 01/29/2016
3, F, 01/31/2016, 02/03/2016
];
Table2:
IntervalMatch(B, A)
LOAD D, E, A
RESIDENT Table2_Temp;
LEFT JOIN (Table2)
LOAD *
RESIDENT Table2_Temp;
DROP TABLE Table2_Temp;
Note: The IntervalMatch function does create a synthetic key in order to link the one date between the other two. This synthetic key is fine and can be kept in your data model.
Thank you for the quick reply!
What if I have another field I need to check on Table 2? how can I incorporate this? would it change everything?
Suppose there's a column G on table2, where it has either "Result" or blank.
so for the column Result, I only want to return a value if column G isn't blank.
Logic:
1. match column A
2. column G = "Result"
3. column B falls in date range (D,E)
Thank you so much!
You should be able to add a where clause at this part:
Table2:
IntervalMatch(B, A)
LOAD D, E, A
RESIDENT Table2_Temp
WHERE G = 'Result';
LEFT JOIN (Table2)
LOAD *
RESIDENT Table2_Temp;
Hi.
See the attached example. In the script when you analyze the dates you can analyze the other.
Hope this help.
Regards.
thank you both, it worked out!