I need to load 2 tables in Qlik Sense and build a 3rd by joining the two of them in a particular way. I'll break the problem into pieces so you can help me step by step.
See this example tables: I need to get de Status in Table 2 given the Code in Table 1.
See that C2 is now C5 and I can track the change through the Description field.
|C1||Ford||C1||Some text C1||Pending|
|C3||VW||C5||Previous code: C2||Received|
|C8||Previous codes: A3 | B2||Pending|
Is it posible to make the join using something similar to "WHERE Table2.Description LIKE %Table1.Code%" ??
What I really need is to make a LEFT JOIN and check first the NewCode field: something similar to "FROM Table1 LEFT JOIN Table2 ON Table1.Code = Table2.NewCode OR Table2.Description LIKE %Table1.Code%".
See that if I made a LEFT JOIN, the Status value for C4 will be NULL. Is it posible to return some other text instead o f NULL?
For example, something like "SELECT Table1.Code, COALESCE(Table2.Status, 'Out of list') as Status FROM Table1 LEFT JOIN Table2 ON Table1.Code = Table2.NewCode OR Table2.Description LIKE %Table1.Code%".
The final result should be something like:
|C4||Out of list|