9 Replies Latest reply: Sep 16, 2011 4:27 PM by Neville Gerard RSS

    Left join two RESIDENT tables WITHOUT further SQL calls

           I have been searching the forum for hours for a single example of what I need.  Every join statement I have come across does an "if exists" on a SQL load.  I don't want to go back to the database for data that I already have in memory.

       

      I have two resident tables with a common field.  All values of this field appear in one table.  the other table has only *some* of the values.  I need to join them getting all distinct values of the field and a "yes" if a value appears in both and a "no" if it appears in only one.

       

      Tables are all_members and some_members (self explanatory).  The field common to both is MemberID.

       

      I need a load statement that creates a *third* table with two fields - MemberID and the aforementioned 'Yes' or 'No' value.

       

      Help please!!!! 

       

      Thank you!!

        • Re: Left join two RESIDENT tables WITHOUT further SQL calls

          I used the lookup() function. 

           

           

           

          SomeMembers:

          LOAD MemberID,

               Name

          FROM

          [C:\Users\wms\Documents\Temp\testdata.xlsx]

          (ooxml, embedded labels, table is SomeMembers);

           

           

           

           

          AllMembers:

          LOAD MemberID,

               Name,

               if(isnull(lookup('Name', 'MemberID',MemberID, 'SomeMembers')),'No','Yes') as Test

           

           

          FROM

          [C:\Users\wms\Documents\Temp\testdata.xlsx]

          (ooxml, embedded labels, table is AllMembers);

           

          See attacchment.  Not sure that is what you are looking for.

            • Re: Left join two RESIDENT tables WITHOUT further SQL calls

              I really appreciate the fast response!  Unfortunately, I simply do not know enough about the syntax to adapt your suggestion to my needs. 

               

              Your lookup  only produces two tables, and they load from an external data source.  I needed examples of creating a third based on the join results of two resident tables.   I am sure that's a trivial difference to you, but it is a high hurdle for new users.  There are tons of posts here about this very topic and each one has slight differences that vastly change the solution

               

              How about another approach - here is a sample load with simple inline data and an explanation of what I need next

               

               

              ALL_MEMBERS:

              Load * INLINE [

                  MEMBERID, RANDOM1

                  A, a

                  B, a

                  C, a

                  D, a

                  E, a

                  F, a

                  G, a

                  H, a

                  I, a

                  J, a

                  K, a

                  L, a

                  M, a

                  N, a

              ];

               

              SOME_MEMBERS:

              Load * INLINE [

                  MEMBERID, RANDOM2

                  A, a

                  C, a

                  G, a

                  L, a

                  M, a

              ];

               

              I added the "RANDOM*" columns so QV would not simply concatenate the two tables.

               

              I need a table with the following:

              1) All MEMBERID values from ALL_MEMBERS (aliased as anything you want)

              2) 'Yes' if the MEMBERID value from ALL_MEMBERS appears in SOME_MEMBERS, 'No' if it does not.

               

              Thank you so much for your help!  I've been beating my head against my desk all day.  Qlikview never fails to make me feel like a flailing idiot.

                • Left join two RESIDENT tables WITHOUT further SQL calls

                  If you need to have all three tables in the final data model then use Jason's code after you have loaded both tabled.

                   

                  Mapping load is a temporary table that goes away after a reload.

                   

                  ALL_MEMBERS:

                  Load * INLINE [

                      MEMBERID, RANDOM1

                      A, a

                      B, a

                      C, a

                      D, a

                      E, a

                      F, a

                      G, a

                      H, a

                      I, a

                      J, a

                      K, a

                      L, a

                      M, a

                      N, a

                  ];

                   

                  SOME_MEMBERS:

                  Load * INLINE [

                      MEMBERID, RANDOM2

                      A, a

                      C, a

                      G, a

                      L, a

                      M, a

                  ];

                   

                   

                  //now use jason's approach  I did not edit the field names but know they need to match your code above.

                  Map_SomeMembers:

                  MAPPING LOAD

                     MemberID,

                     'Yes'

                  RESIDENT some_members;

                  //The table above goes away  after reload

                   

                   

                  //Its important that this table has a different structure than the all members table because QlikView will concatenate it to all members.

                  New_Table:

                  LOAD

                     MemberID,

                     ApplyMap('Map_SomeMembers',MemberID,'No') AS Check

                  RESIDENT all_members;

                   

                   

                   

                  Now you should have 3 tables all linked via MemberID.

                    • Left join two RESIDENT tables WITHOUT further SQL calls

                      Yes, I should have mentioned that having all three tables survive was a key element of my goal.  I had a field in the Some_members table with the value "yes" for all fields so that I could drill into only those members by selecting the only available value for that field in a listbox - but there was no corresponding "no" to link to the other records that appeared in All_members but not Some_Members.  Make sense?  I needed to create this data point to have the binary yes/no to drill into one or both.

                       

                      Thank you so much for the responses.  The activity in this community never ceases to amaze me.  QV obviously has a die hard following. 

                       

                      Thanks again!  Cheers!

                    • Re: Left join two RESIDENT tables WITHOUT further SQL calls
                      Jason Michaelides

                      You need to use "NoConcatenate" to prevent QlikView joining 2 identical tables.  Try this mate:

                       

                       

                      ALL_MEMBERS:

                      Load * INLINE [

                          MEMBERID

                      A

                      B

                      C

                      D

                      E

                      F

                      G

                      H

                      I

                      J

                      K

                      L

                      M

                      ];

                       

                      SOME_MEMBERS:

                      NoConcatenate

                      Load * INLINE [

                          MEMBERID

                          A

                          C

                          G

                          L

                          M

                      ];

                       

                      Map_Members:

                      MAPPING LOAD

                        MEMBERID,

                        'Yes'

                      RESIDENT SOME_MEMBERS;

                       

                      FINAL TABLE:

                      LOAD

                        MEMBERID,

                        ApplyMap('Map_Members',MemberID,'No')  AS  Check

                      RESIDENT ALL_MEMBERS;

                       

                      Stick at it - QlikView is a completely brilliant product with so much awesome functionality!!

                       

                      Jason

                  • Left join two RESIDENT tables WITHOUT further SQL calls
                    Jason Michaelides

                    Assuming all_members and some_members are already loaded earlier in the script:

                     

                    Map_SomeMembers:

                    MAPPING LOAD

                       MemberID,

                       'Yes'

                    RESIDENT some_members;

                     

                    New_Table:

                    LOAD

                       MemberID,

                       ApplyMap('Map_SomeMembers',MemberID,'No') AS Check

                    RESIDENT all_members;

                     

                    That should do it.

                     

                    Jason