Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
