Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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.
Can you share sample excel files? containing 5 to 10 rows of each table?
To test this?
Thanks
Try to do this with your table; it works for me:
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
];
I'm having a little trouble implementing this. My tables have a basic structure like this:
DONOR TABLE
Donor D | FirstName | LastName | Address |
---|---|---|---|
1 | Fname1 | LName1 | Addy1 |
2 | Fname2 | LName2 | Addy2 |
3 | Fname3 | LName3 | Addy3 |
4 | Fname4 | LName4 | Addy4 |
5 | Fname5 | LName5 | Addy5 |
GIFT TABLE
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 |
GIFTUDF TABLE
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 |
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?
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?
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:
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.
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