Hi everyone,
I am trying to join two tables on a condition involving a calculus on fields from both tables.
Let me explain, here are my datasets :
Table1:
Load * Inline [
Gen ID, Q ID, CODE, Amount, Step
AAAA, D13, JABB, 300, ONE
AAAA, D13, JABB, 300, TWO
AAAA, D15, VIBB, 200, ONE
AAAA, D15, VIBB, 110, TWO
CCCC, HA18, CORB, 30, ONE
CCCC, Hsl17, JAMM, 200, ONE
CCCC, Hsl17, JAMM, 100, TWO
CCCC, GA38, BAMM, 94, ONE
CCCC, HAS, BAMM, 100, ONE
CCCC, HAS, BAMM, 270, TWO
CCCC, GA300, GIMM, 39, TWO
CCCC, Hsl17, KAMM, 345, TWO
];
Table2:
Load * Inline [
GenID, Amount, Date
AAAA, 299, 01/02/20
BBBB, 95, 03/10/20
CCCC, 270, 14/12/19
CCCC, 38, 19/10/18
];
What I want to do is to display the dates from Table2 in my Table1 and joining through this condition :
IF ( absolute_value(Table1.Amount - Table2.Amount) = 1 AND Table1.GenID1 = Table2.GenID2)
If there are no matching Date from Table2 for a specify entry in Table1, then just put NULL in Date
Here is the Table I would like to end up with:
Final_Table:
Load * Inline [
Gen ID, Q ID, CODE, Amount, Step, Date
AAAA, D13, JABB, 300, ONE, 01/02/20
AAAA, D13, JABB, 300, TWO, 01/02/20
AAAA, D15, VIBB, 200, ONE, NULL
AAAA, D15, VIBB, 110, TWO, NULL
CCCC, HA18, CORB, 30, ONE, NULL
CCCC, Hsl17, JAMM, 200, ONE, NULL
CCCC, Hsl17, JAMM, 100, TWO, NULL
CCCC, GA38, BAMM, 94, ONE, 03/10/20
CCCC, HAS, BAMM, 100, ONE, NULL
CCCC, HAS, BAMM, 270, TWO, NULL
CCCC, GA300, GIMM, 39, TWO, 19/10/18
CCCC, Hsl17, KAMM, 345, TWO, NULL
];
What I would have done in SQL would have been something like this (I think it would work but I haven't tested it so I might be wrong as I am new to SQL to):
SELECT *
FROM Table1
LEFT OUTER JOIN Table2 ON
abs(Table1.amount - Table2.amount) = 1 AND Table1.GenID1 = Table2.GenID2;
Here is what I tried in Qlik :
LEFT JOIN (Table1)
LOAD *
Resident Table2
WHERE ((Table1.Amount - Table2.Amount = 1) OR (Table1.Amount - Table2.Amount = -1));
But Qlik interpretor does not get the Table1.Amount part. I tried to give my fields two distinc names (Amount1 and Amount2) but it still doesn't work.
I also tried to use a WHERE clause instead of a JOIN, but it implied I should LOAD fields from Table1 and Table2 inside the same expression, so I had two tables inside my RESIDENT clause, and Qlik did not like it neither.
Actually, I think I do not understand well how Qlik deals with Field names and RESIDENT clause.
Could someone, in addition of helping me solve my problem, explain it better to me ?
Thank you very much for your time
Lucas
