topic Re: Group and Keep only the most recent Account in QlikView

Group and Keep only the most recent Account

MalcolmCICWF — Sat, 16 Nov 2024 17:53:20 GMT

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)

Re: Group and Keep only the most recent Account

Vegar — Mon, 02 Nov 2020 17:59:35 GMT

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;

Re: Group and Keep only the most recent Account

MalcolmCICWF — Tue, 03 Nov 2020 21:06:28 GMT

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

Re: Group and Keep only the most recent Account

MayilVahanan — Wed, 04 Nov 2020 01:06:48 GMT

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

Re: Group and Keep only the most recent Account

MalcolmCICWF — Wed, 11 Nov 2020 16:14:23 GMT

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;

Re: Group and Keep only the most recent Account

MalcolmCICWF — Wed, 11 Nov 2020 16:15:36 GMT

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

Re: Group and Keep only the most recent Account

edwin — Thu, 12 Nov 2020 02:00:44 GMT

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;

Re: Group and Keep only the most recent Account

MalcolmCICWF — Fri, 13 Nov 2020 18:20:42 GMT

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