Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Creating formula to identify change based on record id

kw
7 - Meteor

Hello,

Is there a way to have a column created that identifies when a change occurs in data based on sorted date and another column. The data is grouped by building and sorted on date. I want to count if a change has occurred. For each building I want to calculate the number of color changes based on the sorted date.

Example

Data set

BuildingColordate
23G12/16/2015
23G1/17/2016
23R2/18/2016
23G3/1/2016
56R3/1/2016
56G3/12/2016

 

 

required outcome

 

BuildingColorDateChange
23G12/16/20150
23G1/17/20160
23R2/18/20161
23G3/1/20161
56R3/12/20160
56G3/1/20161
5 REPLIES 5
RodL
Alteryx Alumni (Retired)

You would use the Multi-Row Formula tool and create an expression that compares the color in the current row with the color in the previous row (i.e., Row-1) and group by building.

kw
7 - Meteor

Thank you... After playing around, I found the tool at the same time you responded...

 

It was in front of me the entire time.

 

ha ha :-)

 

 

 

Ganbatte
5 - Atom

Hello,

 

I am new to the community.

 

I have the same issue. Can you show me more detail for the solution such as sharing the workflow?

 

Thank you very much.

AngelosPachis
16 - Nebula

Hi @Ganbatte 

 

Have a look at the attached workflow, I may not have grouped on building but the solution is the same.

 

Screenshot 2020-10-06 154613.jpg

 

Hope that helps

Ganbatte
5 - Atom

Thank you @AngelosPachis for your quick turnaround. I appreciate your help.

Labels