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 |
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;
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;
Hi believe this solve the issue:
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.
You want to compare only those rows which have 9 month gap ?
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 |
what output are you expecting
use where clause after resident
tab3:
load
OrderID,Date,Desc,"Show/Hide"
resident tab2
where "Show/Hide"='Show';
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;