Status Tracer calculation

[Neffri]

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: 

 I will be grateful for help 🙂

[atcodedog05]

Hi @Neffri 

Interesting scenario

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

[Qiu]

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

[Neffri]

 Hi,

Here is the sample file.

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

[Qiu]

@Neffri 

Kindly check my workflow.

And Row# should be marked as 2?

[Neffri]

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 · Accepted solution]

Hi @Neffri 

Here is my take. Matches the expected output

Workflow:

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]

Hi @Neffri 

You can accomplish this with a simple multi-row formula

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]

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

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