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. and ID

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 IDCustomer NameSerial No.Trans_Date
135898JUAN CRUZ WE2156123408/27/2015
136622JUAN CRUZWE2156123408/27/2015

 

is it possible i can get this?

Agreement IDCustomer NameSerial No.Trans_Date
136622JUAN CRUZWE2156123408/27/2015

 

Best Regards,

Bing

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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;

View solution in original post

3 Replies
Highlighted

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Partner
Partner

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;

View solution in original post

Highlighted
Creator
Creator

Hello There,

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