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
Solved! Go to Solution.
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
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 |
@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.
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