23 Replies Latest reply: Oct 20, 2017 11:21 AM by Stefan Wühl 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?