Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

misterkingsley
New Contributor III

Creating a Left Outer Join - Qlik Sense

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?

23 Replies
omarbensalem
Esteemed Contributor

Re: Creating a Left Outer Join - Qlik Sense

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;

misterkingsley
New Contributor III

Re: Creating a Left Outer Join - Qlik Sense

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.

omarbensalem
Esteemed Contributor

Re: Creating a Left Outer Join - Qlik Sense

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

To test this?

Thanks

omarbensalem
Esteemed Contributor

Re: Creating a Left Outer Join - Qlik Sense

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

];

misterkingsley
New Contributor III

Re: Creating a Left Outer Join - Qlik Sense

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?

d_manassieva
New Contributor

Re: Creating a Left Outer Join - Qlik Sense

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?

omarbensalem
Esteemed Contributor

Re: Creating a Left Outer Join - Qlik Sense

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

misterkingsley
New Contributor III

Re: Creating a Left Outer Join - Qlik Sense

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

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