Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MalcolmCICWF
Creator III
Creator III

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)

Capture.PNG

1 Solution

Accepted Solutions
edwin
Master II
Master II

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;

View solution in original post

7 Replies
Vegar
MVP
MVP

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;

MalcolmCICWF
Creator III
Creator III
Author

added example of data, minus the other fields that I also need to bring in. 

MayilVahanan

Hi @MalcolmCICWF 

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
MalcolmCICWF
Creator III
Creator III
Author

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;

MalcolmCICWF
Creator III
Creator III
Author

Per Acct_num I only want to keep one and I want it to be the most recent Date for each. 

edwin
Master II
Master II

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;

MalcolmCICWF
Creator III
Creator III
Author

Not sure what was going on, I think it's working right now, I'll check back if it isnt. Thanks