Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
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

Highlighted
Creator
Creator

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

Highlighted
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

 

Highlighted
Creator
Creator

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
Highlighted
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

Highlighted
Creator
Creator

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.

Highlighted
Creator
Creator

i got it thanks