Comparing two tables in Qlik Sense

[Neha121]

Hi All,

    I have two tables table 1 and fact 1. I want to create another final table with missing Product in Fact 1for the Financial year by comparing Fact 1 with Table 1, Table 1 is reference table which has list of all the products. how do I do it in script.

Table 1

Product

P1

P2

P3

P4

Fact 1
Product	FY Year
P1	20-21
P2	20-21
P3	20-21
P4	20-21
P1	21-22
P2	21-22
P4	21-22
P1	22-23
P2	22-23
P3	22-23
P4	22-23
P2	23-24
P4	23-24

Final Table
Product	FY Year
P3	21-22
P1	23-24
P3	23-24

[madelonjansen · Accepted solution]

Hi Neha,

I'd say something like this:

first, join the products from T1 to all years in F1, to have all possible combinations:

Temp1:
Load Distict [FY Year]
Resident Fact1;

Join (Temp1)
Load Product
Resident Table1:

 Then create a key in both tables to compare:

Temp2:
Load *, Product &'|'& [FY Year] as KEY_X
Resident Temp1;
Drop table Temp1;

Fact2:
Load *, Product &'|'& [FY Year] as KEY_Y
Resident Fact1;
Drop table Fact1;

Next do another resident of Temp to filter out existing values. And cleanup after:

Final:
Noconcatenate
Load *
Resident Temp2
Where Not Exists(KEY_Y, KEY_X);
Drop Table Temp2; 

Drop Fields KEY_X, KEY_Y;