The 13th part of our new series "Topic Thursday" with valuable tips and insights around the topic Alteryx. The articles are written by our colleague Alexander Gross, Alteryx ACE and Business Intelligence Consultant & Alteryx Lead at M2.
In many datasets we need not only one RecordID, but one RecordID per unique value of a column, e.g. if we have the datasets of several countries and want to number them per country. How to do that, I explain in this article.
One possibility would be to build a batch macro with a RecordID tool, but it is also much easier with a multi-row formula. Many beginners first sort the data and then solve this similar to Excel with the following formula:
IF [Row-1:Spalte] != [Spalte] Then 1 Else [Counter] + 1 Endif
In Alteryx this can be done even easier with the grouping function, after that it only needs the following function:
[Row-1:Counter] + 1
Why? With the group function all data is divided into blocks. For example, for a country column, there could be 100 data rows for 'Germany', 200 for 'England' and 300 for 'Switzerland'. Then the formula is executed for each block individually. The default setting for 'Values that don't exist' is already '0 or Empty', so the counter row before our first row of the block is filled with 0 and the current or first row becomes 1. All further rows and blocks are calculated as expected and get their counter exactly as we would expect.
Have fun trying it out!
 |
Every second Thursday is Topic Thursday: our colleague Alexander Gross, Business Intelligence Consultant & Alteryx Lead at M2, shares his expert knowledge on workflow building in Alteryx. With his tricks and tips, you can use Alteryx even more efficiently. |
Do you have any questions about this article or about Alteryx in general? Then please feel free to contact us at any time. We look forward to exchanging ideas with you.
Your M2 team
Phone: +49 (0)30 20 89 87 010
info@m2dot.com · M2@Facebook · M2@Twitter · M2@LinkedIn · M2@Instagram