Multi row formula question

[RichardJacobson]

Hi, I'm struggling with solving a problem within Alteryx.

First time posting, sorry if this is in the wrong place.

Thanks

Rich

Here is the data and the outcome I'm looking for.  Using the Description I want to create a column Tier. 

I was able to create a column that just has the 0-3 (= 1), 3+ (=99999/Last), but can't figure out the rest.

Data:

Acct          Service          Description                         Price

123A          DIU               # of Users (0 - 3)               $0  

123A          DIU               # of Users (3+)                  $1

456B          ACT              # Checks (0 - 500)            $0.50  

456B          ACT               # of Users (501 - 1000)    $0.45

456B          ACT               # of Users (1001 - 5000)  $0.40  

456B          ACT               # of Users (5000+)           $0.35

Output / results

Acct          Service          Description                         Price     Tier

123A          DIU               # of Users (0 - 3)               $0          1

123A          DIU               # of Users (3+)                  $1          2

456B          ACT              # Checks (0 - 500)            $0.50     1

456B          ACT               # of Users (501 - 1000)    $0.45     2

456B          ACT               # of Users (1001 - 5000)  $0.40     3

456B          ACT               # of Users (5000+)           $0.35     4

[NicoleJohnson]

You should be able to group by Acct and/or Service in your example in the Multi-Row tool, then create a new field called "Tier" and use the following formula:

[Row-1:Tier]+1

This will add one to your tier with every row, but then reset it when it hits a new Acct/Service combo. Hope that helps! 

Cheers,

NJ

[RichardJacobson]

Thanks Nicole,

What a easy solution. I'm guessing this only works if the data is already sorted correctly?

If my data was not sorted correctly how would I solve for that?

Resorting my data with alteryx would do the following:

Acct          Service          Description                         Price   

123A          DIU               # of Users (0 - 3)               $0        

123A          DIU               # of Users (3+)                  $1        

456B          ACT              # Checks (0 - 500)            $0.50   

456B          ACT              # Checks (1001 - 5000)    $0.40     

456B          ACT              # Checks (5000+)             $0.35     

456B          ACT              # Checks (501 - 1000)      $0.45     

Output would be: which is not correct.

Acct	Service	Decsription	Tier
123A	DIU	# of Users (0 - 3)	1
123A	DIU	# of Users (3+)	2
456B	ACT	# Checks (0 - 500)	1
456B	ACT	# Checks (1001 - 5000)	2
456B	ACT	# Checks (5000+)	3
456B	ACT	# Checks (501 - 1000)	4

Correct output would be:

Acct	Service	Decsription	Tier
123A	DIU	# of Users (0 - 3)	1
123A	DIU	# of Users (3+)	2
456B	ACT	# Checks (0 - 500)	1
456B	ACT	# Checks (1001 - 5000)	3
456B	ACT	# Checks (5000+)	4
456B	ACT	# Checks (501 - 1000)	2

[tom_montpool]

@NicoleJohnsongave you a great solution.

In the interest of giving different solutions, try adding a new field (e.g. Constant) to your table and fill it with 1's.

Then you can use the Running Total tool and use the Group By settings to select your Acct & Service fields. This will return a field called RunTot_Constant (or the name of the field you filled with 1's).

You could also use the Tile tool with the Unique Value method. This might also help with your sort order question.

[danrh · Accepted solution]

To get the sort correct, you need to read those ranges in the parentheses in as numbers, rather than a string.  I'd suggest using Regex to pull out the first number and then sorting by that new field.  Something like:

Hope it helps!

[RichardJacobson]

Thanks Danrh!

I just figured that out and then got your message.  Your's is simpler than what I cam up with but about the same.  Thanks so much for all the help!!

Rich