Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 |
It Worked. Really Appreciated..
try this
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
hi @Ahidhar
I have some issues with this Solution few OrderIDs which is not repeated are also hidden.
Shall we connect quickly If possible..
which OrderIDs could you share some data
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..
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;
Thanks, @Ahidhar It looks good now..
What condition were we where missing before?
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