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

Topic of Interest

Accepted answers

danrh

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!

solution.yxmd

All comments

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,

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

hhaigh1 280