Find last transaction date time prior to reload time

[johngouws]

Good morning. 

I have a extract that loads data for a number of sites early in the morning. Part of what I need to do is show the last transaction timestamp per site. Unfortunately the sites have the ability to post transactions for a future date. The below snippet will give you an idea of what the data looks like. 

What I need to do is not show the transaction timestamp for the future dated transactions. In essence, I need to show the max timestamp prior to the reloaded timestamp. The reason for this is, if looking at site PV00, if their comms went down for 2 days, I would be under the impression that everything is fine, when in fact it is not. 

My Time Since expression is currently: INTERVAL(DATE(reloaded,'hh:mm.ss') - DATE(max(Trandatetime)),'hh:mm.ss')

I would appreciate any thoughts on this. 

[tresesco]

Essentially, you are looking to hide/supress/exclude those lines that have negative interval result/diff. If you need help in 'how', try to explain bit more on how and where (i.e. in chart or script) ... you are expecting this 

[johngouws]

Reading it again, it was not a very clear explanation. 

Lets me try to explain it better. The reload time is "2023-03-29 11:16:58" and the max Transaction time is "2023-04-22 00:00:00", but unfortunately the max Transaction time is in the future. In reality, the transaction time prior to the reload time is "2023-03-28 14:53:00". What I would like to show in the chart under Last Trandate is "2023-03-28 14:53:00" and not the future dated transaction date. Then in the Since last expression I would like to show the difference between "2023-03-28 14:53:00" and reload time "2023-03-29 11:16:58". 

At this stage I am using the below script to partly achieve the result. Unfortunately with this script I loose the future transactions. I am also using Now() in the where clause because I can't get my variable $(reloaded) to work. 

tmp:
LOAD
Rec_id,
Site,
date(Trandatetime,'YYYY-MM-DD hh:mm:ss') as Trandatetime
FROM [$(vRAW_QVDFolder)\RAW_Data.qvd](qvd)
Where date(Trandatetime,'YYYY-MM-DD hh:mm:ss') < date(Now(),'YYYY-MM-DD hh:mm:ss');

If it is possible to achieve this in the expression, it will be first prize. However, if I need to create a flag in the script, that will also work. 

[marcus_sommer · Accepted solution]

You need to store by each reload the tran-dates and loading them again as mapping-table to be able to replace a future date with the valid date from a previous load. Means within the current load you compare the tran-date against now() and if it's lesser you take it and otherwise you pull the previous value from the mapping.

[johngouws]

Thanks. I obviously can't get it to work with historical loads but I've set it up and did a few test runs. Works perfectly,