Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to load some data that has multiple Acct_Num, but I only want to keep the most recent by Create_Date so that I end up with the most recent record. I'm trying the below as well as some others, but no fix yet. Anyone able to help me out with the most specific way to get what I need?
Table1:
LOAD
ACCT_NUM,
Method,
ESTATEMENT,
Max(CREATE_DATE) as MostRecentRecord
Resident Table_Optimized
Group By ACCT_NUM,Method,ESTATEMENT,CREATE_DATE;
Drop Table Table_Optimized;
EDIT: (added example of sample data , I want most recent record of each Acct #, also need to load the other fields)
these suggested solutions should work. what are you observing? can you add a sample QVW? i tested it out and it works (i just got one line):
noconcatenate
table_Optimized:
LOAD * inline [
Acct_num,Response_Method,DISPOSITION_CODE,CREATE_DATE
A1, A, B, 5/1/2020
A1, D, C, 5/2/2020
A1, E, D, 5/3/2020
A1, F, E, 5/4/2020
];
//////////////////////////////////
INNER JOIN(table_Optimized)
LOAD
Acct_num,
Max(CREATE_DATE) as CREATE_DATE
Resident table_Optimized
Group By Acct_num;
Your requirement could be intrpreted in more than one way, but I give it a try. You could try this.
Table1:
LOAD
ACCT_NUM,
Max(CREATE_DATE) as CREATE_DATE
Resident Table_Optimized
GROUP BY ACCT_NUM;
LEFT JOIN
LOAD
ACCT_NUM,
Method,
ESTATEMENT,
CREATE_DATE
Resident Table_Optimized;
added example of data, minus the other fields that I also need to bring in.
Try like below
Inner Join(Table_Optimized)
LOAD
ACCT_NUM,
Max(CREATE_DATE) as CREATE_DATE
Resident Table_Optimized
Group By ACCT_NUM;
We are getting the latest created date based on Account number and inner join with your original table.
so, its load only the latest created date information per Accounts.
Hope it helps
Neither of these are working... I am still getting multiple accounts back... Not sure what is going on here guys, been stuck trying to get this seemingly simple section to work.
table_Optimized:
LOAD
Acct_num,
INPUT_SOURCE as Response_Method,
DISPOSITION_CODE,
ESTATEMENT,
CREATE_DATE
FROM qvd
WHERE MixMatch(INPUT_SOURCE,'INB','MAIL','WEB','MSRCH','OUTB')>0 ;
//////////////////////////////////
INNER JOIN(table_Optimized)
//////////////////////////////////
LOAD
Acct_num,
Max(CREATE_DATE) as CREATE_DATE
Resident table_Optimized
Group By Acct_num;
Per Acct_num I only want to keep one and I want it to be the most recent Date for each.
these suggested solutions should work. what are you observing? can you add a sample QVW? i tested it out and it works (i just got one line):
noconcatenate
table_Optimized:
LOAD * inline [
Acct_num,Response_Method,DISPOSITION_CODE,CREATE_DATE
A1, A, B, 5/1/2020
A1, D, C, 5/2/2020
A1, E, D, 5/3/2020
A1, F, E, 5/4/2020
];
//////////////////////////////////
INNER JOIN(table_Optimized)
LOAD
Acct_num,
Max(CREATE_DATE) as CREATE_DATE
Resident table_Optimized
Group By Acct_num;
Not sure what was going on, I think it's working right now, I'll check back if it isnt. Thanks