JOIN with the key within a field

[#1]

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