0 Replies Latest reply: Jul 20, 2017 2:58 PM by Elliot Outland 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