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?
Maybe like
T1:
LOAD GiftID,
MissionFlag
FROM
[https://community.qlik.com/thread/257283]
(html, codepage is 1252, embedded labels, table is @3)
WHERE MissionFlag ='N';
Inner JOIN
LOAD GiftID,
DonorID,
GiftType
FROM
[https://community.qlik.com/thread/257283]
(html, codepage is 1252, embedded labels, table is @2);
T2:
LOAD [Donor D] as DonorID,
FirstName,
LastName,
Address
FROM
[https://community.qlik.com/thread/257283]
(html, codepage is 1252, embedded labels, table is @1)
WHERE NOT EXISTS(DonorID,[Donor D]);
DROP TABLE T1;
swuehl Would T2 need to have [Donor D] and DonorID flipped?
So:
T2:
LOAD
DonorID as [Donor D],
FirstName,
LastName,
Address
FROM...
I think that's only a typo in your original post, isn't it? And that's why I needed to alias the field name.
But if it's not, I think it should look like I've written it, [Donor D] is the field name from your input source.
Hah, you're correct. a typo indeed. I knew I shouldn't have tried correcting a master!
Thanks.
I've read up some on the Exists function, but not sure I understand how it knows where to look for other instances of the DonorID field. Do you mind explaining this?
Another option would be (similar to what was suggested above):
T1:
LOAD GiftID,
MissionFlag
FROM
[https://community.qlik.com/thread/257283]
(html, codepage is 1252, embedded labels, table is @3)
;
JOIN
LOAD GiftID,
DonorID,
GiftType
FROM
[https://community.qlik.com/thread/257283]
(html, codepage is 1252, embedded labels, table is @2);
JOIN
LOAD [Donor D] as DonorID,
FirstName,
LastName,
Address
FROM
[https://community.qlik.com/thread/257283]
(html, codepage is 1252, embedded labels, table is @1)
;
INNER JOIN
LOAD DonorID WHERE Flag;
LOAD DonorID, If( Only(MissionFlag)='Y',1,0) as Flag
RESIDENT T1
GROUP BY DonorID;
Let me explain :
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
];
// In this first step above, we have 2 tables; GIFTUDF and GIFTS table;
what I want to do is joining the 2 tables into ONE TABLE.
The table GIFTUDF will be our base table; which means, that we will import the WHOLE GIFTUDF table
then join to it only rows of the seconds table which have GiftID existing in the GIFTUDF table.
NoConcatenate
final:
load * where Flag='Y';
load * where Flag<>null();
load DonorID, ONLY(MissionFlag)as Flag Group by DonorID;
load * Resident GIFTUDF;
// This is preceeding loads.
Let me explain this;
First of all, there are 2 types of loading;
1) we can can load data from external table, the syntax would be:
load * FROM table:
2) we can can load data from internal table, a resident table,the syntax would be:
load * RESIDENT table:
This is what we're doing here ; The first step is loading everything from the newly created table (combination of GIFTUDF and the second table; the resulting table will have as a name ; the name of the base table which is GIFTUDF)
NoConcatenate
final:
load * Resident GIFTUDF;
Now, this has become our new source;
I want to load from it some things; to work with, (donorID and FLAG) so a do a proceeding load:
NoConcatenate
final:
2) load DonorID, ONLY(MissionFlag)as Flag Group by DonorID;
1)load * Resident GIFTUDF;
This is like saying, load donorID, and ONLY one FLAG by DONOR from the newly created table (load * resident GIFTUDF)
With this, if a donorID has only one FLAG, we will load the FLAG, else, we will load the field FLAG as a NULL value;
So we want to suppress these null values; to only leave the DonorID with only one FLAG:
NoConcatenate
final:
3) load * where Flag<>null();
2) load DonorID, ONLY(MissionFlag)as Flag Group by DonorID;
1) load * Resident GIFTUDF;
We now have donorID which only have ONE FLAG (Y or N) (not both):
Let's only KEEP flag Y:
NoConcatenate
final:
4) load * where Flag='Y';
3) load * where Flag<>null();
2) load DonorID, ONLY(MissionFlag)as Flag Group by DonorID;
1) load * Resident GIFTUDF;
The result is a table which contains DonorID, and FLAG for only the donors who only have one FLAG=Y.
Let's now have the informations in the donor table joining our new base table:
That means;
let's load only from DONOR table, the rows that have DonorID existing in our final table (3 and 5)
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
];
Let's now have the informations left in the table GIFTUDF (giftID, GiftType) joining our new base table:
That means;
let's load only from GIFTUDF table, the rows that have DonorID existing in our new final table (3 and 5)
left Join(final)
load DonorID,GiftID,GiftType Resident GIFTUDF;
drop table GIFTUDF;
result:
The exists() functions checks against the symbol table of the field DonorID, it checks if the value has been loaded and added to the symbol table so far (i.e. up to but not including the currently loaded record).
That's a helpful article from Henric, thanks.
Thanks for the explanation!
Working on implementing now...
I switched over to using the QVDs (my load was taking too long). So my script now looks like this:
T1:
LOAD
"Gift ID",
"Gift Mission Flag"
FROM GiftUDF.QVD
(qvd)
WHERE "Gift Mission Flag" ='N';
Inner JOIN
LOAD
"Gift ID",
"Donor ID",
"Gift Record Type"
FROM Gift.QVD
(qvd);
T2:
LOAD
"Donor ID",
"First Name",
"Last Name"
FROM Donor.QVD
(qvd)
WHERE NOT EXISTS("Donor ID","Donor ID");
DROP TABLE T1;
So, this should return a table, T2, of donors that do not show up in the T1 List of Donors?