12 Replies Latest reply: Apr 24, 2017 7:43 AM by Shaun Hinds RSS

    Joining Tables with Blank Values

    Shaun Hinds

      Hello Qlik Community,

          I would like to join Status in Table 2 to Table 1 based on the criteria in Table 2 where not blank.

      If I use a left join Table 2 Code, Type, Model; status is only added to ID's 3 and 5.

      If I use a left join Table 2 Code, Type; status is added to ID's 2, 3, 5, 7,9 (good) as well as ID 6 (bad).

            Additionally ID's 3 and 5 just happen to have the same status. I would expect issues if they were different.

       

      Sorry for the poor synatx/short-hand. I hope I conveyed what I'm trying to accomplish.

      Thanks for your help. Shaun.

       

      Table1:

      IDCodeTypeModel

      1

      Arthur

      Tangerine

      9
      2ArthurCarrot
      3WalterTangerine

      9

      4WalterCarrot
      5WalterTangerine6
      6WalterTangerine8
      7CorrinaSprite4
      8CorrinaTangerine9
      9CorrinaSprite

       

       

      Table2:

      CodeTypeModelStatus
      ArthurCarrotOpen
      WalterTangerine9Closed
      WalterTangerine6Closed
      CorrinaSpriteOpen

      Status of all others should be 'Pending', but I can live with a blank

       

      Combined Table should look like:

      IDCodeTypeModelStatus
      1ArthurTangerine9Pending
      2ArthurCarrotOpen
      3WalterTangerine9Closed
      4WalterCarrotPending
      5WalterTangerine6Closed
      6WalterTangerine8Pending
      7CorrinaSprite4Open
      8CorrinaTangerine9Pending
      9CorrinaSpriteOpen
        • Re: Joining Tables with Blank Values
          Muñoz Héctor

          Hi Shaun,

           

          Try the following:

           

          MAP_STATUS:

          MAPPING LOAD Code & '|' & Type & '|' & Model     AS MAP_KEY

                                     Status                                        AS MAP_DESC

          RESIDENT TABLE2;

           

          TABLE1:

          LOAD .....

                    .....

                    ApplyMap('MAP_STATUS', Code & '|' & Type & '|' & Model, 'Pending')          AS Status

          FROM xxxxx;

           

          Regards,

          H

            • Re: Joining Tables with Blank Values
              Shaun Hinds

              Thanks Héctor. That did it.

              • Re: Joining Tables with Blank Values
                Shaun Hinds

                New question. How can I use a wildmatch where blanks exist in Table2 (or should I be using something else)?

                Basically, Table2 contains a blank, I don't care what that value is in Table1. I want Status based on only populated fields in Table2

                 

                For example, if ID 7 in Table1 has a Model = 4 whereas ID 9 has no Model, the Status in the resulting table would have ID 7 as Pending, but ID 9 as Open.

                  • Re: Joining Tables with Blank Values
                    Muñoz Héctor

                    Hi Shaun,

                    Then, if I donot miss anything, you should use an IF statement like:

                     

                    If(Len(Model) = 0,

                       'Pending',

                       ApplyMap('MAP_STATUS', Code & '|' & Type & '|' & Model, 'Pending'))     AS Status

                     

                    , here if a record has Model without information you always put "Pending" literal; in other case you use tha ApplyMap() function.

                     

                    Hope it serves!

                     

                    Regards,

                    H

                    • Re: Joining Tables with Blank Values
                      Anna Pochyla

                      You could try using second applymap function, that will ignore Model value from table1.

                       

                      ApplyMap('MAP_STATUS', Code & '|' & Type & '|' & Model, ApplyMap('MAP_STATUS', Code & '|' & Type & '|', 'Pending') )          AS Status

                        • Re: Joining Tables with Blank Values
                          Shaun Hinds

                          Thanks for the suggestions guys.

                          Héctor,

                              Your first reply was spot on for that question. So  thanks for that help. I understand where you are going with your reply to my second question. However, if any field is blank in Table2, that means I don't care what the corresponding value in Table1 is. I can be any value or even blank. I hope that helps clarify what I'm trying to achieve.

                           

                          Anna,

                             Thanks for your reply. I think you understand what I'm trying to accomplish in my second question. I expect your solution will work in this situation. However, I anticipate in the future I will need to add more fields to Table1 and Table2. If I need to add 10 more fields, I would need to have multiple nested ApplyMap statements for every combination. I feel that there must be a more elegant solution to allow for expansion of the number of fields without, having an exponential grow in the size of my nested statements. Let me know if that doesn't make sense.

                           

                          Thanks to both of you for your help, time and patience. Shaun.

                            • Re: Joining Tables with Blank Values
                              Muñoz Héctor

                              Hi Shaun,

                               

                              If I have understood it well you only want those records coming from TABLE2 where they have not any empty or null value, have you? Then you could create MAP_STATUS table with a WHERE condition like below:

                               

                              MAP_STATUS:

                              MAPPING LOAD Code & '|' & Type & '|' & Model     AS MAP_KEY

                                                         Status                                        AS MAP_DESC

                              RESIDENT TABLE2

                              WHERE      SubStringCount('|' & Code & '|' & Type & '|' & Model & '|', '||') = 0;

                               

                              With this condition: SubStringCount('|' & Code & '|' & Type & '|' & Model & '|', '||') = 0 we will not take into account those records with two pipes in a row; and 2 pipes in a row using a pipe as a separator of key fields mean that any of the key fields is empty or null. I donot know if I can explain it propoerly...

                               

                              Hope it serves!!!

                               

                              Regards,
                              H

                                • Re: Joining Tables with Blank Values
                                  Shaun Hinds

                                  Thanks Héctor. I think your code will exclude any rows from Table2 from the Map. That means that  ID in the Combined table will be mapped as Pending. I want to map based on any fields that are populated in Table2. If the field is not populated in Table2, I still want to map to Table1 but based only the remaining fields.

                                   

                                  If I could Map Table 2 with a wildcard like * instead of null(), but I don't think we can map wildcards, correct?

                                   

                                  I update the Tables in the origianl post to add a Model to ID 7. There will likely be more Fields added in the future, but for the items already mapped in Table2, those additional fields would also be mapped as * to not effect the prior results.

                                   

                                  I don't think  I'm explainnig this very well. Sorry.

                                    • Re: Joining Tables with Blank Values
                                      kushal chawda

                                      see this

                                       

                                      Table2:

                                      mapping LOAD trim(Code)&trim(Type) as Model ,

                                          Status

                                      FROM

                                      [https://community.qlik.com/thread/257637]

                                      (html, codepage is 1252, embedded labels, table is @2)

                                      where len(trim(Model))=0;

                                       

                                      Table2_1:

                                      mapping LOAD trim(Code)&trim(Type)&trim(Model) as Model ,

                                          Status

                                      FROM

                                      [https://community.qlik.com/thread/257637]

                                      (html, codepage is 1252, embedded labels, table is @2)

                                      where len(trim(Model))>0;

                                       

                                      Table1:

                                      LOAD *,

                                               if(Status1='Pending' and Status2<>'Pending' ,Status2,

                                                if(Status1<>'Pending' and Status2='Pending' ,Status1,Status2)) as Status;

                                      LOAD ID,

                                          Code,

                                          Type,

                                          Model,

                                          applymap('Table2',trim(Code)&trim(Type),'Pending') as Status1,

                                          applymap('Table2_1',trim(Code)&trim(Type)&trim(Model),'Pending') as Status2

                                      FROM

                                      [https://community.qlik.com/thread/257637]

                                      (html, codepage is 1252, embedded labels, table is @1);


                                        • Re: Joining Tables with Blank Values
                                          Shaun Hinds

                                          Thanks Kushal Chawda. That works. In a case where I 'filter' based on perhaps only one field, I would think I would have to created additional variants of your mapping load and applymap, correct? For example, if Table1 and Table2 were shown as shown below, to create the combined table your script would also add the following.

                                           

                                          This seems lik the script could quickly grow larger as the number of fields in Table2 increases. If I had 10,000 IDs and 8 fields in Table2 that may or may not be blank, I would need to add a new mapping Load, apply map and if statements for each cobination of those 8 fields, correct?

                                           

                                          One more question, is there a particular reason you used trim? Is that a best practice or something else? Just curious.

                                           

                                          Thanks again. Shaun.

                                           

                                          Table2:

                                          ...

                                           

                                          Table2_1:

                                          ...

                                           

                                           

                                          Table2_2:

                                          mapping LOAD trim(Code) as Model ,

                                              Status

                                          FROM...

                                          where len(trim(Model))=0 and len(trim(Type))=0;

                                           

                                          Table1:

                                          LOAD *,

                                                   if(Status3<>'Pending',Status3,

                                                    if(Status1='Pending' and Status2<>'Pending' ,Status2,

                                                    if(Status1<>'Pending' and Status2='Pending' ,Status1,Status2))) as Status;

                                          LOAD ID,

                                              Code,

                                              Type,

                                              Model,

                                              applymap('Table2',trim(Code)&trim(Type),'Pending') as Status1,

                                              applymap('Table2_1',trim(Code)&trim(Type)&trim(Model),'Pending') as Status2

                                              applymap('Table2_2',trim(Code),'Pending') as Status3

                                          FROM

                                           

                                           

                                          Table1:

                                          IDCodeTypeModel

                                          1

                                          Arthur

                                          Tangerine

                                          9
                                          2ArthurCarrot
                                          3WalterTangerine

                                          9

                                          4WalterCarrot
                                          5WalterTangerine6
                                          6WalterTangerine8
                                          7CorrinaSprite4
                                          8CorrinaTangerine9
                                          9CorrinaSprite
                                          10VictorCarrot
                                          11VictorTangerine8
                                          12VictorSprite4
                                          13Victor

                                          Sprite

                                           

                                           

                                          Table2:

                                          CodeTypeModelStatus
                                          ArthurCarrotOpen
                                          WalterTangerine9Closed
                                          WalterTangerine6Closed
                                          CorrinaSpriteOpen
                                          VictorClosed

                                           

                                          Combined Table:

                                          IDCodeTypeModelStatus
                                          1ArthurTangerine9Pending
                                          2ArthurCarrotOpen
                                          3WalterTangerine9Closed
                                          4WalterCarrotPending
                                          5WalterTangerine6Closed
                                          6WalterTangerine8Pending
                                          7CorrinaSprite4Open
                                          8CorrinaTangerine9Pending
                                          9CorrinaSpriteOpen
                                          10VictorCarrotClosed
                                          11VictorTangerine8Closed
                                          12VictorSprite4Closed
                                          13VictorSpriteClosed
                                            • Re: Joining Tables with Blank Values
                                              kushal chawda

                                              Number of IDs would not make a difference until you have very large data set. Also in your case, there will be a fix key like code, model and type based on which you would do the mapping so number of fields also doesn't matter.

                                                • Re: Joining Tables with Blank Values
                                                  Shaun Hinds

                                                  Thanks Kushal Chawda.  It is clear that number of IDs doesn't matter.I only mention that because as the number of IDs increases, the number of fields I will want to sort by will also increase. Table2 (where I'm pulling my Status map) could easily end up having 200+ rows  with 20 different fields to sort on. Each row will have a different mixture of sorting fields.

                                                  For the example with the current fields, the script only requires the following field combinations for 1) Map Loading, 2)nested if statements and 3) apply map:

                                                  -Code  where Type & Model are null

                                                  -Code & Type where Model is null

                                                  -Code, Type & Model where none are null

                                                   

                                                  The number of possible combinations will at a minumum be squared, then multiply that again for each variant within each field. I'm not saying this is the case, but it could grow there.

                                                   

                                                  If I add more fields and combinations it can easily exapnd to:

                                                  -Code  where (Type, Model, Color, Size, Brand, Location, Season) are null()

                                                  -Code & Type where (Model, Color, Size, Brand, Location, Season) are null()

                                                  -Code, & Model where (Type, Color, Size, Brand, Location, Season) are null()

                                                  -Code & Color  where (Type, Model, Size, Brand, Location, Season) are null()

                                                  -Code, Brand, & Location  where (Type, Model, Color, Size, Season) are null()

                                                  -Code, Model, & Season  where (Type, Color, Size, Brand, Location) are null()

                                                  -Code, Size  where (Type, Model, Color, Size, Brand, Location, Season) are null()

                                                  -Code, Brand, & Size where (Type, Model, Color, Location, Season) are null()

                                                  -Code, Color, Location where (Type, Model, Size, Brand, Season) are null()

                                                  -Code, Type, Model & Season where (Color, Size, Brand, Location) are null()

                                                  ...

                                                   

                                                  In that case, I would have to have 10 Map load scripts, 10 nested if statements, 10 applymap functions.

                                                  For each addition field combination added to Table2, I would need to revise the script and add that new field  combination to the script.

                                                   

                                                  Perhaps this is turning in to a string issue instead? I'm just throwing ideas out there. Thanks again for your help. Shaun