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

[Dharanidharan_Qlik]

It Worked. Really Appreciated.. 

[TcnCunha_M]

try this

tab:

load * Inline

[

OrderID,Date,Desc

6789,11/17/2023,gchc

6789,8/17/2023,nhrth

6789,1/17/2023,gchc

6789,11/18/2022,etyehet

6789,10/12/2022,gchc

6790,10/17/2023,defg

6790,5/17/2023,defg

6790,12/23/2022,dfdf

];

tab1:

NoConcatenate

Load 

OrderID,Date,Desc

resident  tab

order by OrderID,Date;

Drop Table tab;

Final:

Load

*

where match(Check,-1,1) > 0; 

Load

*,

Date &'|'& OrderID As %KeyOrderAndDate,

If(OrderID =  Previous(OrderID) ,

if( Date- Previous(Date) >= 201 , 1, 0) , -1) as Check

Resident     tab1;

Drop Table tab1;

Exit Script;

[Dharanidharan_Qlik]

Hi @Ahidhar 

Small Doubt.. How a field date1 can be used before it's exits or created .. Like in the below image you said.. 

We are creating Date1 field but using it inside the field.. is there any helping document available for this concept

[Ahidhar]

that can be done by peek function it works with the output data

see  Example 2 hope it helps

Peek

[Dharanidharan_Qlik]

hi @Ahidhar  

I have some issues with this Solution few OrderIDs which is not repeated are also hidden.

Shall we connect quickly If possible..

[Ahidhar]

which OrderIDs could you share some data 

[Dharanidharan_Qlik]

Below Image.. OrderID 1Z7R65990304296833 repeats but both are marked as hide.. and other 3 OrderID occur only once it also marked as hide.. I got more than 30k similar case like this

Attached Few samples data along with this 4 Order ID.. some works fine some not..

[Ahidhar]

try this

tab:
LOAD
OrderID,
date("Date",'DD/MM/YYYY') as Date
FROM [lib://DataFiles/Sample Data (1).xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
tab1:
load *
Resident tab order by OrderID,Date desc;drop table tab;

tab2:
load *,
if(Date1=previous(Date1) and OrderID=Previous(OrderID),'Hide','Show') as "Show/Hide";
load *,
if(OrderID=Previous(OrderID) and
if(day(peek(Date1))-day(Date)<0,
(year(peek(Date1))*12-year(Date)*12+month(peek(Date1))-month(Date))-1,
(year(peek(Date1))*12-year(Date)*12+month(peek(Date1))-month(Date)))<9,
peek(Date1),Date) as Date1
resident tab1;drop table tab1;

[Dharanidharan_Qlik]

Thanks, @Ahidhar  It looks good now..

What condition were we where missing before?

[Ahidhar]

these two if statements

if(Date1=previous(Date1) and OrderID=Previous(OrderID),'Hide','Show') as "Show/Hide"; 

if(day(peek(Date1))-day(Date)<0,
(year(peek(Date1))*12-year(Date)*12+month(peek(Date1))-month(Date))-1,
(year(peek(Date1))*12-year(Date)*12+month(peek(Date1))-month(Date)))<9