Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
bsbernabe
Creator
Creator

Duplicate customer and serial No. get the last trans_date

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 IDCustomer NameSerial No.Trans_Date
135844ROMEO TORRES2DY101123403/06/2015
136622ROMEO TORRES2DY101123403/07/2015
137827MARCELO PRASASKB22206642403/25/2015
137706BOYET BAYG123-20636603/24/2015
138188MARCELO PRASASKB22206642403/26/2015
138541BOYET BAYG123-20636603/27/2015
17999ROLLY PEROSXRM16E21791501/26/2014
83971ROSE GANXRM16E21700501/06/2014
84235LEONARDO CAMANOJEZCCC0715301/10/2014

 

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

Agreement IDCustomer NameSerial No.Trans_Date
136622ROMEO TORRES2DY101123403/07/2015
138188MARCELO PRASASKB22206642403/26/2015
138541BOYET BAYG123-20636603/27/2015
17999ROLLY PEROSXRM16E21791501/26/2014
83971ROSE GANXRM16E21700501/06/2014
84235LEONARDO CAMANOJEZCCC0715301/10/2014

 

Best Regards,

Bing

Labels (1)
1 Solution

Accepted Solutions
Arthur_Fong
Partner
Partner

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

View solution in original post

7 Replies
Arthur_Fong
Partner
Partner

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

View solution in original post

bsbernabe
Creator
Creator
Author

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 IDCustomer NameSerial No.Trans_Date
135844ROMEO TORRES2DY101123403/06/2015
136622ROMEO TORRES2DY101123403/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 IDCustomer NameSerialTrans_Date
136622ROMEO TORRES2DY101123403/07/2015
146672JOSEPH ONG2DY101123406/22/2017

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

Best Regards,

Bing

Arthur_Fong
Partner
Partner

How do you identify which row is a resale?

Is this 2 rows classified as resale?

135844ROMEO TORRES2DY101123403/06/2015
136622ROMEO TORRES2DY101123403/07/2015

 

bsbernabe
Creator
Creator
Author

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

135844ROMEO TORRES2DY101123403/06/2015
136622ROMEO TORRES2DY101123403/07/2015
Arthur_Fong
Partner
Partner

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:

MC.PNG

bsbernabe
Creator
Creator
Author

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
Creator
Creator
Author

i got it thanks