Hello!
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.
| Table1 | | | Table2 | | |
| Code | Brand | | NewCode | Description | Status |
| C1 | Ford | | C1 | Some text C1 | Pending |
| C2 | GM | | C3 | Some text | Received |
| C3 | VW | | C5 | Previous code: C2 | Received |
| C4 | GM | | C7 | Some text | Pending |
| | | C8 | Previous codes: A3 | B2 | Pending |
| | | C9 | Some text | Pending |
1st Problem:
Is it posible to make the join using something similar to "WHERE Table2.Description LIKE %Table1.Code%" ??
2nd Problem:
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%".
3rd Problem:
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:
| Result | |
| Code | Owner |
| C1 | Pending |
| C2 | Received |
| C3 | Received |
| C4 | Out of list |
Thank you!!
