Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Status Tracer calculation

Neffri
8 - Asteroid

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 🙂

 

10 REPLIES 10
atcodedog05
22 - Nova
22 - Nova

Hi @Neffri 

 

Interesting scenario

 

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

 

Qiu
20 - Arcturus
20 - Arcturus

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

0104-Neffri.PNG

Neffri
8 - Asteroid

 Hi,

Here is the sample file.

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

Qiu
20 - Arcturus
20 - Arcturus

@Neffri 

Kindly check my workflow.

And Row# should be marked as 2?

Neffri
8 - Asteroid

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'.

 

atcodedog05
22 - Nova
22 - Nova

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 😀👍

danilang
19 - Altair
19 - Altair

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

Neffri
8 - Asteroid

@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.

Neffri
8 - Asteroid

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

Labels