Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Need to find out nearest date for one date column based another column.
I am having 2 date fields one is order date and another is shipped date.
I need to find out nearest shipped date for the order date.please find the sample data.
Hi,
try this:
Data:
LOAD
date(date#(X, 'MM/DD/YYYY'), 'MM.DD.YYYY') AS X,
date(date#(Y,'MM/DD/YYYY'), 'MM.DD.YYYY') AS Y,
RoWNO() AS %KEY
INLINE [
X, Y
04/24/2024, 03/11/2024
04/24/2024, 04/20/2024
05/13/2024, 05/13/2024
02/21/2024, 02/14/2024
02/21/2024, 03/05/2024
02/21/2024, 02/01/2024
06/06/2024, 06/06/2024
01/24/2024, 01/27/2024
01/24/2024, 01/01/2024
];
LEFT JOIN(Data)
LOAD Y AS Y2,
IterNo() AS %KEY
RESIDENT Data
WHILE IterNo() <= NoOfRows('Data');
MAP_Delta:
MAPPING LOAD %KEY & '-' & MIN(fabs(X - Y2)),
1
Resident Data
GROUP BY %KEY;
NoConcatenate
TEMP:
LOAD *,
fabs(X - Y2) AS Delta
Resident Data
WHERE APPLYMAP('MAP_Delta', %KEY & '-' & fabs(X - Y2), 0);
;
DROP Table Data;
Best regards Son
Thank you for reply,But I can't change script as it is huge dataset .I need frontend expression
Hi @MS90 ,
Try something like :
Data :
Load *
from xyz;
Left Join(Data)
Order_Key &'|'&num(Order_Date) as OKey,
min(FABS(Order_Date-Shipped_Date)) as Diff
Resident Data group by Order_Key &'|'&num(Order_Date);
Noconcatenate
Final:
Load *,
Order_Date+Diff as Closest_Date
Resident Data;
Drop table Data;
Let me know if it works.
Regards,
Rohan.
Edit : I later saw it to be a front end problem :
Try This :
Order_Date + Aggr(min(FABS(Order_Date-Shipped_Date)),Order_Key,Order_Date)
Regards,
Rohan.
Thanks Rohan,I can't change script as it is huge data ,I need frontend expression,the above expression is not working
Hi @MS90 ,
Can you share a screenshot of the same ?
Hi Roshan, I tried this expression -firstsortedvalue(distinct Shipped_date,fabs(open_date-Shipped_Date)) it is working but while adding in Table it is not ignoring un matched values.showing entire values, in totals row it is showing result
try something like this, use your Actual column name and table
Load
S.NO,
ORDERDate ,
Min(SHipdate) as nearestshipdate
Resident table Group by
S.NO,
ORDERDate order by SHipdate asc;