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
Anonymous
Not applicable
Author

swuehl

Was revisiting this today and had a question.

What the the Where clause do when there is no expression attached, as in the example you provided above:

INNER JOIN

LOAD DonorID WHERE Flag;

LOAD DonorID, If( Only(MissionFlag)='Y',1,0) as Flag

RESIDENT T1

GROUP BY DonorID;

swuehl
MVP
MVP

Flag is either 1 or 0.

1 will be interpreted as TRUE and 0 as FALSE by QlikSense, and that is what the WHERE clause will use to filter the records.

If you want, you can also write

LOAD DonorID WHERE Flag=1;

Anonymous
Not applicable
Author

So the Where Flag is essentially just saying "Where Flag is True"?

swuehl
MVP
MVP

The HELP would express it like

where criterion

wherewhere is a clause used for stating whether a record should be included in the selection or not. The selection is included if criterionis True.

criterion is a logical expression.

A logical expression can be as simple as a field name Flag, if the field values can be interpreted as a logical value. As said, numbers not equal zero will be interpreted as TRUE, zero as FALSE.