Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Not applicable

JOIN with the key within a field

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.

Table1Table2
CodeBrandNewCodeDescriptionStatus
C1FordC1Some text C1Pending
C2GMC3Some textReceived
C3VWC5Previous code: C2Received
C4GMC7Some textPending
C8Previous codes: A3 | B2Pending
C9Some textPending

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
CodeOwner
C1Pending
C2Received
C3Received
C4Out of list



Thank you!!

Community Browser