Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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 |
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 |
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:
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.
i got it thanks