0 Replies Latest reply: Mar 22, 2016 1:47 PM by Julio F RSS

    JOIN with the key within a field

    Julio F

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