Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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;

Anonymous
Not applicable
Author

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

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

To test this?

Thanks

OmarBenSalem

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

];

Anonymous
Not applicable
Author

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?

Anonymous
Not applicable
Author

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

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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