Help with firstsortedvalue and fabs

[hopkinsc]

Hi all,

I have a key field (made up of a code and Allocated_Date) and a TransactionDate field. 
What i am trying to do is for each TransactionDate find all of the Allocated_Dates which are <= to it and return the max. 

So if i have the following Allocated Dates..

05/03/2019 11:57:42

11/03/2019 10:05:23

14/03/2019  07:05:31

14/03/2019 07:45:01

and i have a list of TransactionDates,

13/03/2019 08:30:47

13/03/2019 08:30:59

13/03/2019 08:31:55

13/03/2019 08:37:40

14/03/2019 09:47:01

14/03/2019 10:11:04

What i would want to see is...

for the first TransactionDate i would find all of the Allocated_Dates <= 13/03/2019 08:30:47 which would be..

05/03/2019 11:57:42

11/03/2019 10:05:23

Then i want the max of the above, so my result would be 11/03/2019 10:05:23

I would want the same value for all of the TransactionDates 13/03/2019 xx:xx:xx as the 11/03/2019 in the highest timestamp <= the 13/03/2019 xx:xx:xx.

for the next 2 TransactionDates..

14/03/2019 09:47:01

14/03/2019 10:11:04

I would want the returned value to be 14/03/2019 07:45:01 as this is the highest timestamp <= the above 2 TransactionDates.

I have tried using..

Timestamp(firstsortedvalue(Allocated_Date,fabs(Allocated_Date-TransactionDate)))

This does the opposite of what i want, so for the TransactionDates of ...

13/03/2019 08:30:47

13/03/2019 08:30:59

13/03/2019 08:31:55

13/03/2019 08:37:40

it returns 14/03/2019 07:05:08 which is the min of the dates >= TransactionDate(s)

So i guess i need to do the opposite of the above expression. 

Can anyone help please?

I have already tried..

Timestamp(firstsortedvalue(Allocated_Date,fabs(-Allocated_Date-TransactionDate)))

Timestamp(firstsortedvalue(Allocated_Date,-fabs(Allocated_Date-TransactionDate)))

Timestamp(firstsortedvalue(-Allocated_Date,fabs(Allocated_Date-TransactionDate)))

Any help would be appreciated. 

[hopkinsc]

Bump.... 🙂

[jaibau1993]

Hi!

I don't know how data is related in your problem but I would use Aggr instead FirstSortedValue. Please, find the attached file where I think I solve your issue. Hope it helps!

Bests,

Jaime.