Duplicate customer and serial No. get the last trans_date

[bsbernabe]

Hello There,

My concern is how can i do this in qlikview  If Customer Name and Serial No. is duplicate then get the last trans_date at expressions or loading script .

Here my sample data from DB:

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

supposed to be output: pls. let me how to do this.

Agreement ID	Customer Name	Serial No.	Trans_Date
136622	ROMEO TORRES	2DY1011234	03/07/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

Best Regards,

Bing

[Arthur_Fong · Accepted solution]

Try this at script level:

Raw:
load [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
];

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

left JOIN(Data)
load * resident Raw;

drop table Raw;
exit script;

Thanks and regards,

Arthur Fong

[bsbernabe]

Thank you so much for your fast support but i forgot to mention that as much as possible i don't want to use date(max(Trans_Date) cause this serial No. can be possible re-sale so we need  the history for the Serial No.

sample: if the customer name and serial No. is duplicate get the last trans_date :

Agreement ID	Customer Name	Serial No.	Trans_Date
135844	ROMEO TORRES	2DY1011234	03/06/2015
136622	ROMEO TORRES	2DY1011234	03/07/2015

like below  will see the output also the history of serial No if having resale transaction happen the customer name and Agreement ID will diff.

Agreement ID	Customer Name	Serial	Trans_Date
136622	ROMEO TORRES	2DY1011234	03/07/2015
146672	JOSEPH ONG	2DY1011234	06/22/2017

may be can do this at set analysis in expression it is possible?

Best Regards,

Bing

[Arthur_Fong]

How do you identify which row is a resale?

Is this 2 rows classified as resale?

135844	ROMEO TORRES	2DY1011234	03/06/2015
136622	ROMEO TORRES	2DY1011234	03/07/2015

[bsbernabe]

this is not resale it is data entry error they re entry twice same name same serial but diff. agreement ID

135844	ROMEO TORRES	2DY1011234	03/06/2015
136622	ROMEO TORRES	2DY1011234	03/07/2015

[Arthur_Fong]

I think the script I provided has covered through all the requirements listed.

Did you try running on your end and see if there is any discrepancies?

I selected the serial no you provided as sample and able to get the historical data:

[bsbernabe]

Yes i already use your recommendation and its great cause its running base on my concern highly appreciated but my problem now I'm using qvd. and  i don't know how to apply base on your script Load then  inline.

[bsbernabe]

i got it thanks