Conditional Logic- Remove Duplicate orders which is repeated within the 9 months

[Dharanidharan_Qlik]

Hi,

I have an order table with more than 20 columns with 5 years of data,  OrderID should not repeat in 9 Months, 

If the order ID is repeated with a one or two-month gap we need to load only the latest row..and remove others that are all duplicates.. 

If the order is repeated in more than a 9-month gap we need to load that.

Example: Here in the below table 11/17/2023 is the latest update row so we need to show that.. the date 10/17/2023 with a 1-month gap is a duplicate so hide.. then the date with a 1/17/2023 has a 9-month gap from the previous date (10/17/2023) so it's not a duplicate we need to show that.. then following date has a 3-month gap so hide it.

Qlik Sense Business 

OrderID	Date	Desc	Show/Hide
6789	11/17/2023	gchc	Show
6789	10/17/2023	nhrth	Hide
6789	1/17/2023	gchc	Show
6789	11/18/2022	etyehet	Hide
6789	10/12/2022	gchc	Hide

[Ahidhar · Accepted solution]

try this ,

tab:
load * Inline
[
OrderID,Date,Desc
6789,11/17/2023,gchc
6789,10/17/2023,nhrth
6789,1/17/2023,gchc
6789,11/18/2022,etyehet
6789,10/12/2022,gchc
6790,11/17/2023,defg
6790,1/17/2023,defg
];

tab1:
mapping load
OrderID,
max(Date) as maxdate
Resident tab
group by OrderID;

tab2:
load *,
if(OrderID<>Previous(OrderID) and Date=ApplyMap('tab1',OrderID),1,0) as Flag,
if(day(previous(Date))-day(Date)<0,
(year(previous(Date))*12+month(previous(Date)))-(year(Date)*12+month(Date))-1,
(year(previous(Date))*12+month(previous(Date)))-(year(Date)*12+month(Date))) as diff
resident tab;
drop table tab;

tab3:
load
OrderID,
Date,
Desc
resident tab2
where Flag=1 or diff>=9;
drop table tab2;

[TcnCunha_M]

Hi believe this solve the issue:

//Load and sort the data 

Tmp:

Load 

OrderID, Date(Floor(Dates))As Dates, Desc

;

Load * inline 

[OrderID, Dates, Desc, Show/Hide

6789, 17/11/2023, gchc, Show

6789, 17/10/2023, nhrth, Hide

6789, 17/01/2023, gchc, Show

6789, 18/11/2022, etyehet,Hide

6789, 12/10/2022, gchc, Hide

];

Order_Data:

NoConcatenate

 Load *,

 If(Previous(OrderID) = OrderID,

  If(Interval( Previous(Dates)-Dates, 'DD') > 270, 1,0),-1) As [Check Keep or Remove Row], // 1  -Keep, 0 , Remove -1 , it's not the same OrderId

 OrderID &'|'&Floor(Dates)  As %KeyRecord ;

Load

OrderID,

Dates,

Desc

resident  Tmp

order by OrderID,

Dates

desc         ;

drop table Tmp;

[Dharanidharan_Qlik]

Thanks, @Ahidhar ,

it worked...

if we need to compare the same with the latest displayed show row what will be the changes? Without comparing the previous date row compare the latest show row has 9 month gap.

[Ahidhar]

You want to compare only those rows which have 9 month gap ?

[Dharanidharan_Qlik]

Something Like this.. latest row is the default show .. and by comparing that 9-month gap will be shown.. Again by comparing the previous show row needs to check the condition

OrderID	Dates	Desc	Show/Hide
6789	17/11/2023	gchc	Show
6789	17/05/2023	nhrth	Hide
6789	17/01/2023	gchc	Show
6789	18/07/2022	etyehet	Hide
6789	12/01/2022	gchc	show
6789	12/10/2021	gchc	Hide

[Ahidhar]

what output are you expecting

[Dharanidharan_Qlik]

[Ahidhar]

use where clause after resident

tab3:
load
OrderID,Date,Desc,"Show/Hide"
resident tab2
where "Show/Hide"='Show';

[Ahidhar]

here is the whole script

tab:
load * Inline
[
OrderID,Date,Desc
6789,11/17/2023,gchc
6789,10/17/2023,nhrth
6789,1/17/2023,gchc
6789,11/18/2022,etyehet
6789,10/12/2022,gchc
6790,11/17/2023,defg
6790,1/17/2023,defg
];

tab1:
mapping load
OrderID,
max(Date) as maxdate
Resident tab
group by OrderID;

tab2:
load *,
if(Flag=1 or diff>=9,'Show','Hide') as "Show/Hide";

load *,
if(OrderID<>Previous(OrderID) and Date=ApplyMap('tab1',OrderID),1,0) as Flag,
if(day(previous(Date))-day(Date)<0,
(year(previous(Date))*12+month(previous(Date)))-(year(Date)*12+month(Date))-1,
(year(previous(Date))*12+month(previous(Date)))-(year(Date)*12+month(Date))) as diff
resident tab;
drop table tab;

tab3:
load
OrderID,Date,Desc,"Show/Hide"
resident tab2
where "Show/Hide"='Show';
drop Table tab2;