Join table with duplicate entries conditioned on first table

[#1]

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!!!

[Nicole-Smith · Accepted solution]

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.

[#3]

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!

[Nicole-Smith]

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;

[spividori]

Hi.

See the attached example. In the script when you analyze the dates you can analyze the other.

Hope this help.

Regards.

[#6]

thank you both, it worked out!