topic Re: Join table with duplicate entries conditioned on first table in QlikView

Join table with duplicate entries conditioned on first table

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Join table with duplicate entries conditioned on first table

Nicole-Smith — Mon, 14 Nov 2016 20:26:27 GMT

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.

Re: Join table with duplicate entries conditioned on first table

Mon, 14 Nov 2016 20:39:29 GMT

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!

Re: Join table with duplicate entries conditioned on first table

Nicole-Smith — Mon, 14 Nov 2016 20:44:13 GMT

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;

Re: Join table with duplicate entries conditioned on first table

spividori — Mon, 14 Nov 2016 20:48:36 GMT

Hi.

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

Hope this help.

Regards.

Re: Join table with duplicate entries conditioned on first table

Mon, 14 Nov 2016 22:09:10 GMT

thank you both, it worked out!