Tracking Date Changes and adding Status Column

[sagarghimire]

How can I best achieve this in Alteryx Workflow?  A-D are available in data set. Each week additional data will be added (union) to prior weeks data set.

record id for given snapshot dt is unique.

Intent is to get status code field added to this historical data set, column E. 

Pull In = latest delivery date < previous delivery date
Push out = latest delivery date > previous delivery date
New = record id does not exist in previous week
latest = max snapshot dt
previous = one less than max snapshot dt

Thanks in advance.

[MatthewO · Accepted solution]

@sagarghimire it looks like you can implement this logic using a combination of sorting and the Multi-Row Formula tool. Attached is a sample workflow to assist.

[sagarghimire]

Thanks Matthew.  It works when I apply this to entire historical data set! 

Since I will probably have many 1000's records each snapshot_dt in reality, do I have to apply this logic after entire data set is built. I was wondering if there is a way to keep flag for data older than current snapshot_dt, and just compare current snapshot_dt data with previous snapshot_dt data and add flag to just most recent snapshot_dt data. Hope I am making sense, let me know your thoughts?

[MatthewO]

@sagarghimire the workflow above requires the full data set. You could modify it to only process the most recent snapshot but it would require that you reference a second file (or other storage) and overwrite it when the workflow runs. I've attached a sample. Keep in mind that the Alteryx engine is very efficient at processing large volumes of records. I wouldn't expect significant performance impact with thousands of records. You could also explore turning on the AMP engine which may drastically increase the time it takes to process the data.