Need to find out nearest date for one date column based another column

[MS90]

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.

 

[PhanThanhSon]

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

[MS90]

Thank you for reply,But I can't change script as it is huge dataset .I need frontend expression

[Rohan]

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.

[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.

[MS90]

Thanks Rohan,I can't change script as it is huge data ,I need frontend expression,the above expression is not working 

[Rohan]

Hi @MS90 ,

Can you share a screenshot of the same ?

[MS90]

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

[SunilChauhan]

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;