- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Group and Keep only the most recent Account
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)
- Tags:
- qlikview_scripting
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
Qlik Community MVP
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
added example of data, minus the other fields that I also need to bring in.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Please close the thread by marking correct answer & give likes if you like the post.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Per Acct_num I only want to keep one and I want it to be the most recent Date for each.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Not sure what was going on, I think it's working right now, I'll check back if it isnt. Thanks