Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
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
Specialist III
Specialist III

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
Partner
Partner

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;

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
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
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
Specialist III
Specialist III

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

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