How to get the previous date last transaction value based on current selected date

[Vikram21]

I am trying to fetch the previous date last transaction value based on date and time. 

Here is the sample data 

ID	Location	DateTime	Value
101	NY	1/1/24 10:28	10
101	NY	3/1/24 22:30	20
101	NY	3/1/24 10:15	70
102	CY	10/1/24 9:10	15
102	CY	15/1/2024 22:18	50
102	CY	15/1/2024 22:20	100

whenever user selects any date and ID,it should display previous date's last transaction value for that particular ID and current selected date value should be replaced with previous date last transaction value for the same ID

For Example If i select ID as 101 and Location as NY and date as 3/1/24 it should display as below 

101	NY	3/1/24	10
101	NY	3/1/24	10

if i select ID as 102 ,LOC as CY and  Date 15/1/24 ,it should display as below 

102	CY	15/1/2024 22:18	15
102	CY	15/1/2024 22:20	15

Any suggestions are appreciated. 

Thanks.

[pravinboniface]

One way to do it is through the load script so that you have previous transaction value on the same row

// Get the Previous value
// Note: Sort order is important for this code
Tab:
load 	ID, Location, DateTime, Value,
		if (ID=peek('ID') and Location=peek('Location'),
        	if (Date(floor(timestamp#(DateTime,'MM/DD/YY hh:mm')),'MM/DD/YY')<>Date(floor(timestamp#(peek(DateTime),'MM/DD/YY hh:mm')),'MM/DD/YY'), 
            		peek(Value), peek('PrevValue')),
            			Null()
            )as PrevValue
Inline [
ID,	Location,	DateTime,	Value
101,	NY,	1/1/24 10:28,	10
101,	NY,	3/1/24 22:30,	20
101,	NY,	3/1/24 10:15,	70
102,	CY,	10/1/24 9:10,	15
102,	CY,	15/1/2024 22:18,	50
102,	CY,	 15/1/2024 22:20,	100
];

exit Script;

[Vikram21]

Hi Pravin,

Is there any other workaround, somehow the above code is not working.

Thanks.

[pravinboniface]

Could you kindly share a screenshot of the data model viewer if you run the code or some other screenshot, please?

[Vikram21]

Hi Pravin,

I tried applying in my dashboard but it's not fetching correct previous last loaded date. For example

ID,	Location,	DateTime,	Value
101,	NY,	1/1/24 10:28,	10
102,    CY      12/12/2022,  649
101,	NY,	3/1/24 22:30,	20
101,	NY,	3/1/24 10:15,	70
102,	CY,	10/1/24 9:10,	15
102,	CY,	15/1/2024 22:18,50
102,	CY,	 15/1/2024 22:20,100
];​

 In the above data, if i select date as 10/1/2024, ID as 102 and Location as CY it has show me value 649 which had the transaction in 12/12/2022. It has to pick whichever the last transaction in the past somehow this is what i am trying to achieve.

[pravinboniface · Accepted solution]

@Vikram21 As I mentioned earlier, sort order is important for this code.  However, as the input data does not have any sorting, I modified to sort.  Some more things to point out.  Your data for timestamp is inconsistent. The date 12/12/2022 does not have a hh:mm.  Some rows has dd/mm/yy and other rows have dd/mm/yyyy. There is no , after CY for one of the rows.  Also, I think you are using DD/MM/YYYY format.

I corrected the data and ran the following.  Please let me know if that works for you please.

// Get the Previous value
// Note: Sort order dependency has been removed
// Note: It looks like data is in DD/MM/YYYY format

Tab:
load *,Date(floor(timestamp#(DateTime,'DD/MM/YYYY hh:mm')),'MM/DD/YY') as myDate             
inline [
ID,	Location,	DateTime,	Value
101,	NY,	1/1/2024 10:28,	10
102,    CY, 12/12/2022 10:10, 649
101,	NY,	3/1/2024 22:30,	20
101,	NY,	3/1/2024 10:15,	70
102,	CY,	10/1/2024 9:10,	15
102,	CY,	15/1/2024 22:18,50
102,	CY,	15/1/2024 22:20,100
];
SortedTab:
load 	ID, Location, myDate, Value,
		if (ID=peek('ID') and Location=peek('Location'),
        	if (myDate<>peek(myDate), 
            		peek(Value), peek('PrevValue')),
            			Null()
            )as PrevValue
resident Tab
order by ID, Location, myDate;

drop table Tab;

exit Script;

[pravinboniface]

@Vikram21 Did this work for you?

[Vikram21]

Thank you so much! It worked.