2 Replies Latest reply: Sep 28, 2017 2:29 PM by Felip Drechsler RSS

    Joining Tables Using Wildcards

    Elliot Outland

      Hey Community,

       

      I'm trying to map a table of clients and codes corresponding to product elections with a table of products and the code that implies that the product in question has been purchased. Hopefully the tables below will clear up what I mean:

       

      Clients:                                                                                 

      ClientMapping Code
      A1200
      B20140
      C01100
      D32140

       

      Products:

      Mapping CodeProduct IDProduct Name
      1*0a
      2*0a
      1*1c
      2*2d
      ?11*3e
      ?2*4f
      3?14*5g

       

      where the Product mapping code follows the standard convention of '?' representing any single character and '*' any single or combination of characters.

       

      I was able to employ the "Mapping With WIldcards" recipe from Rob Wunderlich's site (Qlikview Cookbook | Recipes for Qlikview Success) within a while loop (see attached QVW), followed by a Left Join to achieve what I guess is effectively a "Wild Left Join":

       

      "Joining" the above tables in this manner yielded precisely what I sought, when the appropriate fields were places in a table box:

      ClientProduct IDProduct Name
      A0a
      A1c
      A4f
      B0a
      B2d
      C3e
      D4f
      D5g

       

      However, when I try to use the same method on non-dummy data (say, a  545-row list of products and a 25000-row list of clients), I don't get an error during the reload, but when I create the table box, I see that many clients are not properly mapped to their respective products. For example, client 'A' in the above tables might only be mapped to products 1 and 4, or even just product 1.

       

      Would anyone be able to offer any thoughts as to why this might be happening?

       

      Thank you in advance for your help.

       

      Thanks,

       

      Elliot

        • Re: Joining Tables Using Wildcards
          Suresh Babu

          Have you got solution

          • Re: Joining Tables Using Wildcards
            Felip Drechsler

            Hi Elliot,

             

            I've done a simple solution for it, but the problem is it joins all the products into the client list, creating a whole lot of extra rows that wouldn't need to be created but at the end, only the needed rows are left in the final table.

             

            Since you have few lines of products and clients, i believe it will perform ok, but as it escalates it will be a nightmare.

            Bellow the code:

             

            Products:

            Load * Inline

            [

            Client,Mapping Code

            A, 1200

            B, 20140

            C, 01100

            D, 32140

            ];

             

            MappingCode:

            Load * Inline

            [

            Mapping, Product ID, Product Name

            1*, 0, a

            2*, 0, a

            1*, 1, c

            2*, 2, d

            ?11*, 3, e

            ?2*, 4, f

            3?14*, 5, g

            ];

             

            join(Products)

            Load

            Mapping

            Resident MappingCode;

             

            x:

            Load

            *,

            WildMatch([Mapping Code],Mapping) as [Flag Mapped]

            Resident Products;

             

            left join(x)

            Load

            Mapping,

            [Product ID],

            [Product Name]

            Resident MappingCode;

             

            NoConcatenate

            finalTable:

            Load

            *

            Resident x

            where [Flag Mapped] = 1;

             

            drop tables Products,MappingCode,x;

             

            drop fields [Mapping Code],Mapping,[Flag Mapped] from finalTable;

             

            Felipe.