Need to control a recursive with a Timestamp

JennieElf · August 2020

I'm doing a recursive to build client data over time and using the Date of incoming calls as the match column to determine duplicates to be excluded. However, sometimes the data changes, so what was going to be 2.7% on 8/15 has been changed to 3.5% on 8/15... hence the datestamp.

How do I build into my recursive the instruction to always keep the data with the most recent timestamp for that date?

JennieElf · August 2020

Sorry, I didn't finish my thought! Will requiring a match between both the Date AND the Timestamp overcome that problem? I'm thinking it will but I'm brand new to DOMO, so would like some advice.

jaeW_at_Onyx · August 2020

your scenario is unclear. please define what one row means in your input dataset.

if i had to guess the granularity is

one row per phone call. and it sounds like the phoneCall date can be updated after the first recording of the phone call.

If that is correct you can APPEND New_Data and Historical_Data, then use WINDOW&RANK to ROW_NUMBER your data, PARTITION BY phoneCall_ID and Order by TimeStamp.

Then FILTER where RowNumber = 1

JennieElf · August 2020

@jaeW_at_Onyx Thanks for the response!

The granularity for a row on this particular dataset is the percentage of forecast calls for the day which are anticipated to come in at a particular hour. For example, 08/15/22 17:30 is 0.00002. However the forecast can change and 08/15/22 17:30 may change to 0.00012. I need to make sure that change happens in the data rather than creating a duplicate.

jaeW_at_Onyx · August 2020

'you can APPEND New_Data and Historical_Data, then use WINDOW&RANK to ROW_NUMBER your data, PARTITION BY phoneCall_ID and Order by TimeStamp.'

Make your partition at the granularity of the data (dateTime it sounds like) order by timestamp.

Need to control a recursive with a Timestamp

Comments

Categories