Alteryx Designer Discussions

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

Status Tracer calculation

7 - Meteor

Hi, 

I need to add Column 'Tracker' which will have 3 different statuses based on the 3 conditions:

  • 0 = Never had an B (status for all months = 'A' or is blank)
  • 1 = Has an B as of the month ( status for the month = 'B')
  • 2 = Had an B (status in any month on the tracker = B)

Sample below: 

 
 

sample.PNG

 I will be grateful for help 🙂

 

17 - Castor

Hi @Neffri 

 

Interesting scenario

 

Edit: Can you provide this sample data as excel file.

 

15 - Aurora

@Neffri 
Appreciate if you would provide with Data format next time.

0104-Neffri.PNG

7 - Meteor

 Hi,

Here is the sample file.

Sorry, next time I will remember to attached the Data format:)

15 - Aurora

@Neffri 

Kindly check my workflow.

And Row# should be marked as 2?

7 - Meteor

Hi Qiu,

 

Thank you for your solution.

Yes, I'm checking your workflow and the first row (RecordID=1) should have Tracer value =1. First time the value 'B' for this Record appeared in September and for the latest month it's still 'B'.

 

17 - Castor

Hi @Neffri 

 

Here is my take. Matches the expected output

Workflow:

atcodedog05_0-1609763986612.png

 

Hope this helps 🙂 Feel to ask if you have any questions


If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

17 - Castor
17 - Castor

Hi @Neffri 

 

You can accomplish this with a simple multi-row formula

 

w.png

 

All the logic is coded in the formula

If Trim([Value]) in ("A","") and [Row-1:Tracker] = 0 then
	0
ElseIf Trim([Value]) in ("A","") and [Row-1:Tracker] = 1 then
	2
Elseif [Value]= "B" and [Row-1:Tracker] in (0,1) then
	1
else
	2
Endif

 

Dan

7 - Meteor

@atcodedog05 I have only one question regarding your solution. Will this REGEX_Match work if statuses A and B will be replaced with words, for example A would be replaced with "Exception" and B with "Scope" (for example). I've changed these values and it doesn't work now for some reason.

7 - Meteor

@danilang unfortunately I can't open your file because I have older version of Alteryx .

Labels