How to check previous field value?

[salalimabill]

Hi everyone!

I have this scenario, that I need to get the sequence of dates, but it is depending on the previous record. Say sample below:

Item	Date	New Data
A	10/2/2016	10/2/2016
A	8/8/2016	10/1/2016
A	3/5/2016	8/7/2016
B	1/19/2016	1/19/2016

Steps would be:

1. Sort the data by Item Ascending, and Date Descending

2. Create a new field named: New Data, the condition would be: If row item is not the same with previous/above item, then the New Data value should be the row Date, else Get the minus 1 of the previous/above Date. (see data in the table above) - this I don't have yet idea how to come-up New Date values.

Regards,

Bill

[andrewdatakim · Accepted solution]

Hi Bill,

You can use the multi-row formula tool. I set it up the same way as your formula, but you can also use the "Group By" option to reduce the formula for the item check.

Check out the workflow below. You should only have to get rid of the Date converter for it to work for you.

[salalimabill]

Hi Andrew,

Noted for the multi-row formula object. I was wrong providing you the data, because my next concern is that the DATE values is formatted in this YYMMDD. My problem now is that I cannot convert it to date, it keeps on telling me an error.

Could you also help me resolving the problem? I posted it here https://community.alteryx.com/t5/Data-Preparation-Blending/How-to-convert-V-String-to-Date-in-EXPRES...

[andrewdatakim]

Hi Bill,

This should solve what you are looking for. I have three versions in the file now, one for a date source, number source and a string source. Everything should be straight forward. The only issue will be if the date is prior to 2000. Let me know if you have some dates prior and I can rework it.

Regards,

Andrew

[salalimabill]

Hi Andrew,

I could not open the file

 

... but I guess the solution provide is correct already. :) Thanks for the help.

Regards,

Bill

[andrewdatakim]

Hi Bill,

What version of Alteryx are you running, I am on 10.5.9? I can also send you some screen shots of the formulas if that is more helpful.

Regards,

Andrew