19 Replies Latest reply: Apr 18, 2017 11:01 AM by Weston Kingsley RSS

    Creating a Left Outer Join - Qlik Sense

    Weston Kingsley

      Hello all!

       

      I have 3 tables: Donor, Gift, and GiftUDF. The Donor Table has Donor info (DonorID, Name, Address, etc). The Gift Table has Gift Info (Date, Amount, Type, etc.). And the GiftUDF Table has additional Gift Info and various flags (MissionFlag, AlumniFlag, etc).

       

      In our records, we have some Donors that only give gifts to capital (these gift records have a MissionFlag = 'N'), some that give only to missions (these gifts would have a MissionFlag = 'Y'), and some that give to both Missions and Capital (these gifts would have either Y or N depending on the gift).

       

      Here's where I'm having trouble.

       

      How do I retrieve a list of Donors that have ONLY given Missions gifts?

       

      I know how to find the Missions GIFTS themselves, but I don't know how to create a table that ensures I have a list of Donors that have ONLY given a gift to missions and HAVEN'T given a gift with a MissionFlag = 'N'.

       

      What's the best way to find this list of Donors?

        • Re: Creating a Left Outer Join - Qlik Sense
          omar bensalem

          I assume you already have on field in common btwn the table to join them?; maybe ID? if so

           

          Maybe sthing like this?

           

          GiftUDF1:

          load * from source;

           

          GiftUDF: //GiftUDC with only mission fLAG=YES

          Load *  resident GiftUDF1 where MissionFlag='Y';

          DROP TABLE GiftUDF1;

          left join(GiftUDF)

          load * from sourceTable GIFT;

           

           

          left join (GiftUDF)

          load * from sourceTable Donor;

            • Re: Creating a Left Outer Join - Qlik Sense
              Weston Kingsley

              My understanding is that this script will only return Donors that have given a Missions gift, but not ONLY missions gifts. I won't know if any of these Donors have also given a Non-Missions gift.

                • Re: Creating a Left Outer Join - Qlik Sense
                  omar bensalem

                  Can you share sample excel files? containing 5 to 10 rows of each table?

                   

                  To test this?

                   

                  Thanks

                  • Re: Creating a Left Outer Join - Qlik Sense
                    omar bensalem

                    Try to do this with your table; it works for me:

                     

                    Capture.PNG

                    Gift:

                    load * where NewFlag='Y';

                    load * where NewFlag<>null();

                    load ID,ONLY(FLAG)as NewFlag Group by ID;

                    load * Inline [

                    ID, FLAG

                    1, Y

                    1,  N

                    2,  Y

                    3,  Y

                    4,  N

                    ];

                     

                     

                     

                     

                    left Join(Gift)

                    donor:

                     

                     

                    load * Inline [

                    ID,Sales

                    1,1000

                    2,200

                    3,800

                    4,700

                    5,600

                    6,500

                    ];

                      • Re: Creating a Left Outer Join - Qlik Sense
                        Weston Kingsley

                        I'm having a little trouble implementing this. My tables have a basic structure like this:

                         

                        DONOR TABLE

                        Donor DFirstNameLastNameAddress

                        1

                        Fname1LName1Addy1
                        2Fname2LName2Addy2
                        3Fname3LName3Addy3
                        4Fname4LName4Addy4
                        5Fname5LName5Addy5

                         

                         

                        GIFT TABLE

                        GiftIDDonorIDGiftType
                        1011A
                        1021A
                        1031B
                        1042B
                        1052A
                        1062C
                        1073A
                        1083A
                        1093B
                        1104A
                        1114A
                        1125A
                        1135A

                         

                         

                        GIFTUDF TABLE

                        GiftIDMissionFlag
                        101N
                        102N
                        103N
                        104Y
                        105Y
                        106N
                        107Y
                        108Y
                        109Y
                        110Y
                        111N
                        112Y
                        113Y

                         

                        That's the basic layout. All of the tables are loading via Resident Table.

                         

                        In the case above, only Donors 3 and 5 are Missions ONLY donors. 1,2 and 4 each gave at least 1 non-missions gift.

                         

                        Does that help?

                          • Re: Creating a Left Outer Join - Qlik Sense
                            omar bensalem

                            GIFTUDF:

                            load * Inline [

                            GiftID, MissionFlag

                            101, N

                            102, N

                            103, N

                            104, Y

                            105, Y

                            106, N

                            107, Y

                            108, Y

                            109, Y

                            110, Y

                            111, N

                            112, Y

                            113, Y

                            ];

                             

                            left join (GIFTUDF)

                            load * Inline [

                            GiftID, DonorID, GiftType

                            101, 1, A

                            102, 1, A

                            103, 1, B

                            104, 2, B

                            105, 2, A

                            106, 2, C

                            107, 3, A

                            108, 3, A

                            109, 3, B

                            110, 4, A

                            111, 4, A

                            112, 5, A

                            113, 5, A

                            ];

                             

                             

                            NoConcatenate

                            final:

                            load * where Flag='Y';

                            load * where Flag<>null();

                            load DonorID, ONLY(MissionFlag)as Flag Group by DonorID;

                            load * Resident GIFTUDF;

                             

                            Left Join(final)

                            DOnor:

                            load * Inline [

                            DonorID, FirstName, LastName, Address

                            1,  Fname1, LName1, Addy1

                            2, Fname2, LName2, Addy2

                            3, Fname3, LName3, Addy3

                            4, Fname4, LName4, Addy4

                            5, Fname5, LName5, Addy5

                            ];

                             

                             

                            left Join(final)

                            load DonorID,GiftID,GiftType Resident GIFTUDF;

                             

                             

                            drop table GIFTUDF;

                             

                            result:

                            Capture.PNG

                      • Re: Creating a Left Outer Join - Qlik Sense
                        Dimitrinka Manassieva

                        I am a complete novice and can only suggest logic, no syntax.  Would it be helpful to turn the solution the other way round? Identify those donors who have given non-mission gifts and then exclude them from the list in the first table?

                          • Re: Creating a Left Outer Join - Qlik Sense
                            Weston Kingsley

                            Dimitrinka,

                             

                            Absolutely. I too am a beginner and have trouble with the syntax!

                             

                            Your suggestion would work, I just don't know how to do that with the correct syntax. Either way, you'll have to end up looking at the gifts/giftUDF table because that's the only way to determine a "non-missions" gift.

                              • Re: Creating a Left Outer Join - Qlik Sense
                                Bill Markham

                                The Help gives a good explanation of Qlik Joins & Keeps along with their syntax and usage.

                                 

                                https://help.qlik.com/en-US/sense/3.2/Subsystems/Hub/Content/Scripting/combine-tables-join-keep.htm

                                • Re: Creating a Left Outer Join - Qlik Sense
                                  Stefan Wühl

                                  Maybe like

                                   

                                   

                                   

                                  T1:

                                  LOAD GiftID,

                                       MissionFlag

                                  FROM

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

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

                                  WHERE MissionFlag ='N';

                                   

                                   

                                  Inner JOIN

                                  LOAD GiftID,

                                       DonorID,

                                       GiftType

                                  FROM

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

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

                                   

                                   

                                  T2:

                                  LOAD [Donor D] as DonorID,

                                       FirstName,

                                       LastName,

                                       Address

                                  FROM

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

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

                                  WHERE NOT EXISTS(DonorID,[Donor D]);

                                   

                                   

                                  DROP TABLE T1;

                                  • Re: Creating a Left Outer Join - Qlik Sense
                                    omar bensalem

                                    Let me explain :

                                     

                                    GIFTUDF:

                                    load * Inline [

                                    GiftID, MissionFlag

                                    101, N

                                    102, N

                                    103, N

                                    104, Y

                                    105, Y

                                    106, N

                                    107, Y

                                    108, Y

                                    109, Y

                                    110, Y

                                    111, N

                                    112, Y

                                    113, Y

                                    ];

                                    left join (GIFTUDF)

                                    load * Inline [

                                    GiftID, DonorID, GiftType

                                    101, 1, A

                                    102, 1, A

                                    103, 1, B

                                    104, 2, B

                                    105, 2, A

                                    106, 2, C

                                    107, 3, A

                                    108, 3, A

                                    109, 3, B

                                    110, 4, A

                                    111, 4, A

                                    112, 5, A

                                    113, 5, A

                                    ];

                                     

                                    // In this first step above, we have 2 tables; GIFTUDF and GIFTS table;

                                    what I want to do is joining the 2 tables into ONE TABLE.


                                    The table GIFTUDF will be our base table; which means, that we will import the WHOLE GIFTUDF table

                                    then join to it only rows of the seconds table which have GiftID existing in the GIFTUDF table.

                                     

                                     

                                     

                                     

                                    NoConcatenate

                                    final:

                                    load * where Flag='Y';

                                    load * where Flag<>null();

                                    load DonorID, ONLY(MissionFlag)as Flag Group by DonorID;

                                    load * Resident GIFTUDF;


                                    // This is preceeding loads.

                                    Let me explain this;


                                    First of all, there are 2 types of loading;


                                    1) we can can load data from external table, the syntax would be:

                                    load * FROM table:


                                    2) we can can load data from  internal table,  a resident table,the syntax would be:

                                    load * RESIDENT table:


                                    This is what we're doing here ; The first step is loading everything from the newly created table (combination of GIFTUDF and the second table; the resulting table will have as a name ; the name of the base table which is GIFTUDF)


                                    NoConcatenate

                                    final:

                                    load * Resident GIFTUDF;


                                    Now, this has become our new source;


                                    I want to load from it some things; to work with, (donorID and FLAG) so a do a proceeding load:

                                    NoConcatenate

                                    final:

                                    2) load DonorID, ONLY(MissionFlag)as Flag Group by DonorID;

                                    1)load * Resident GIFTUDF;


                                    This is like saying, load donorID, and ONLY one FLAG by DONOR from the newly created table (load * resident GIFTUDF)


                                    With this, if a donorID has only one FLAG, we will load the FLAG, else, we will load the field FLAG as a NULL value;


                                    So we want to suppress these null values; to only leave the DonorID with only one FLAG:

                                    NoConcatenate

                                    final:

                                    3) load * where Flag<>null();

                                    2) load DonorID, ONLY(MissionFlag)as Flag Group by DonorID;

                                    1) load * Resident GIFTUDF;


                                    We now have donorID which only have ONE FLAG (Y or N) (not both):

                                    Let's only KEEP flag Y:


                                    NoConcatenate

                                    final:


                                    4) load * where Flag='Y';


                                    3) load * where Flag<>null();

                                    2) load DonorID, ONLY(MissionFlag)as Flag Group by DonorID;

                                    1) load * Resident GIFTUDF;


                                    The result is a table which contains DonorID, and FLAG for only the donors who only have one FLAG=Y.


                                    Let's now have the informations in the donor table joining our new base table:


                                    That means;

                                    let's load only from DONOR table, the rows that have DonorID existing in our final table (3 and 5)


                                    Left Join(final)

                                    DOnor:

                                    load * Inline [

                                    DonorID, FirstName, LastName, Address

                                    1,  Fname1, LName1, Addy1

                                    2, Fname2, LName2, Addy2

                                    3, Fname3, LName3, Addy3

                                    4, Fname4, LName4, Addy4

                                    5, Fname5, LName5, Addy5

                                    ];

                                     

                                    Let's now have the informations left in the table GIFTUDF (giftID, GiftType)  joining our new base table:


                                    That means;

                                    let's load only from GIFTUDF table, the rows that have DonorID existing in our new final table (3 and 5)

                                    left Join(final)

                                    load DonorID,GiftID,GiftType Resident GIFTUDF;

                                     

                                     

                                    drop table GIFTUDF;

                                     

                                    result:

                                     

                                    Capture.PNG