7 Replies Latest reply: Apr 28, 2016 6:07 AM by annick whitfield RSS

    Qlik Sense - Joining 2 tables, using an expression

    annick whitfield

      Hello,

       

      I would very much appreciate you help in solving this case:

       

      I have these 2 tables which I would like to join, using a "wild" match between the Location (e.g. FR-PARIS) and the Expression (e.g. FR-*) so that I can associate the Group to the Order (e.g. FRANCE) :

       

      Order:

      ORDERIDAmountLocation
      150FR-PARIS
      220FR-NICE
      312IT-ROME
      456IT-MILAN

       

      Grouping:

      GroupExpression
      FRANCEFR-*
      ITALYIT-*

       

       

      I would like to obtain this table at the end:

      OrderWithGrouping:

      ORDERIDAmountLocationGroup
      150FR-PARISFRANCE
      220FR-NICEFRANCE
      312IT-ROMEITALY
      456IT-MILANITALY

       

       

      I am creating the 2 first tables above this way:

       

      Order:

      Load * inline

      [ORDERID, Location,  Amount

      1,       FR-NICE,   200      

      2,       FR-PARIS,  100

      3,       IT-ROME,   300

      4,       IT-VENISE, 400

      ];

       

      Grouping:

      Load * inline

      [Group,  Expression

      FRANCE, FR-*

      ITALY,  IT-*

      ];

       

      I don't know how to join these 2 tables in order to join them and obtain the above table OrderWithGrouping

      It would be very helpful if you could give me some advice. Many thanks

       

      Annick