Duplicate customer and serial No. and ID

[bsbernabe]

Hello There,

My concern today  is similar scenario to my previews post but that is already solved by Arthur highly appreciated His solution. This time the diff. in my previews concern is that  the Customer Name | Serial No. | Trans_Date is all the same but the only  different is  AgreementID if ever how can get the max(AgreementID)? 

Agreement ID	Customer Name	Serial No.	Trans_Date
135898	JUAN CRUZ	WE21561234	08/27/2015
136622	JUAN CRUZ	WE21561234	08/27/2015

is it possible i can get this?

Agreement ID	Customer Name	Serial No.	Trans_Date
136622	JUAN CRUZ	WE21561234	08/27/2015

Best Regards,

Bing

[Anil_Babu_Samineni]

Perhaps this?

T1:
LOAD [Agreement ID],
[Customer Name],
[Serial No.],
Trans_Date
FROM
[https://community.qlik.com/t5/New-to-QlikView/Duplicate-customer-and-serial-No-and-ID/m-p/1641047#M3...]
(html, utf8, embedded labels, table is @1);

Final:
NoConcatenate
LOAD Max([Agreement ID]) as [Agreement ID],[Customer Name],
[Serial No.],
Trans_Date Resident T1 Group By [Customer Name],
[Serial No.],
Trans_Date;

DROP Table T1;

[Arthur_Fong · Accepted solution]

Try this:

Raw:
load AutoNumber([Customer Name]&'|'&[Serial No.]) as Key,
[Agreement ID],[Customer Name],[Serial No.],date#(Trans_Date,'MM/DD/YYYY') as Trans_Date inline [
Agreement ID,Customer Name,Serial No.,Trans_Date
135844,ROMEO TORRES,2DY1011234,03/06/2015
136622,ROMEO TORRES,2DY1011234,03/07/2015
137827,MARCELO PRASAS,KB222066424,03/25/2015
137706,BOYET BAY,G123-206366,03/24/2015
138188,MARCELO PRASAS,KB222066424,03/26/2015
138541,BOYET BAY,G123-206366,03/27/2015
17999,ROLLY PEROS,XRM16E217915,01/26/2014
83971,ROSE GAN,XRM16E217005,01/06/2014
84235,LEONARDO CAMANO,JEZCCC07153,01/10/2014
146672,JOSEPH ONG,2DY1011234,06/22/2017
135898,JUAN CRUZ,WE21561234,08/27/2015
136622,JUAN CRUZ,WE21561234,08/27/2015
];

Data:
NoConcatenate
load distinct
Key,
date(max(Trans_Date),'MM/DD/YYYY') as Trans_Date
Resident Raw
group by
Key;

NoConcatenate
tempRaw:
load [Customer Name],[Serial No.],Trans_Date,
if(rowNo()=1,[Agreement ID],if(peek(Key)=Key,null(),[Agreement ID]))as [Agreement ID],
Key
resident Raw
order by Key desc,
[Agreement ID]desc;

drop table Raw;

left join(Data)
load * resident tempRaw
where not isnull([Agreement ID]);

drop table tempRaw;

EXIT SCRIPT;

[bsbernabe]

Hello There,

Thank You so much.....Highly appreciated you time and support.