best records from a set of records

[fallenangel6]

Hi all,

I have this data

clsid	partyid	accountStatus	accountid	AccountOpeningDate
12111111	123	Active	1000	23-05-2016
12111111	123	Active	5000	24-03-2017
12111111	123	Closed	3000	12-03-2019
12111111	123	Closed	3999	25-09-2016

i want the output this way.

clsid	partyid	accountStatus	accountid	AccountOpeningDate
12111111	123	Active	3000	12-03-2019

best of Account status  and maximum account opening date in same row.

ie,if any record for the same clsid and partyid has Active account status, that must come in the output. and only if all those records have Closed account statuses, the account status in output will be 'Closed'... Please help

[MindaugasBacius]

Here  you go:

tmp:
LOAD clsid, 
     partyid, 
     accountStatus, 
     accountid, 
     Date(Date#(AccountOpeningDate, 'DD-MM-YYYY')) as AccountOpeningDate,
     If(accountStatus = 'Active', 1, 0) as Flag
FROM
[https://community.qlik.com/t5/New-to-QlikView/best-records-from-a-set-of-records/m-p/1544768#M370087]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);


Left Join (tmp)
LOAD clsid,
	partyid,
	Sum(Flag) as SumFlag
Resident tmp
Group By clsid,
	partyid
;


NoConcatenate
tmp2:
LOAD clsid, 
     partyid, 
     Max(AccountOpeningDate) as AccountOpeningDate,
     If(SumFlag > 0, 'Active', 'Closed') as accountStatus
Resident tmp
Group By clsid, 
     partyid, 
     If(SumFlag > 0, 'Active', 'Closed')
;

Left Join (tmp2)
LOAD clsid, 
     partyid, 
     AccountOpeningDate,
     accountid
Resident tmp;

DROP Table tmp;

The results are following:

[sunny_talwar]

You can also try this

Table:
LOAD clsid,
	 partyid,
	 MinString(accountStatus) as accountStatus,
	 FirstSortedValue(accountid, -AccountOpeningDate) as accountid,
	 Date(Max(AccountOpeningDate)) as AccountOpeningDate
Group By clsid, partyid;
LOAD * INLINE [
    clsid , partyid, accountStatus, accountid, AccountOpeningDate 
    12111111, 123, Active, 1000, 23-05-2016
    12111111, 123, Active, 5000, 24-03-2017
    12111111, 123, Closed, 3000, 12-03-2019
    12111111, 123, Closed, 3999, 25-09-2016
];