Today we will see the tool Generate Rows of Alteryx, with which we can generate rows according to a given condition.
To understand the dynamics of this tool we will work on the following case. A company sells certain products monthly, but not all products are sold every month. To make a report and to see clearly which ones are not being sold, it is necessary to complete the information with the missing months and your sales in 0.
This report is one that takes until the month of May 2019. All products should have 5 lines (one for each month)
To begin we bring some very simple data about the monthly sale of certain products.
As we see none of these 3 products brings the 5 months, it is necessary to complete them. For this we will use the tool Generate rows, following these steps.
- In a text input we define the initial date 01/01/2019 and the final one would be the current month of this report (to make it dynamic) As we can see, the generate rows tool created a new column called New Date in which are the 5 months that we want.
2. Now what we need to do is to extract each unique product from the data delivered, using the summarize tool, although it could also be with Unique.
3. We need to link each of the dates with each of the existing products. For this we use the Append Fields tool that allows us to do this. As we see already each product has 5 dates of the corresponding 5 months. Now it is necessary to join it with the original data to bring the corresponding amounts to the months in which there were sales.
4. To bring the corresponding sales column, we must make a join of the product and the date. Since the date of the original data is now, it will not allow us to perform this join, so we first format it using a formula.
5. With this formula we join the date and with the DATETIME tool we give it a date format.
6. We already have the data ready to cross them.
7. With this crossing we have in the exit J, the 8 records that we had in the original data with the sales. In the R exit we have the dates that did not cross because they do not exist and to this data we add a field called Sales Amount and we assign 0. Then we Unite it. As we can see, we achieved our initial objective. We already have 5 records for each month for each product.
How to use the Generate rows to increment alphabetically?
Basically we need an initial and final value that are used in the condition of the Generate rows and the CharttoInt functions to convert letters to numbers and CharFromInt to convert numbers to letters.
- The TexInput contains the beginning and the end.
2. We configure the Generate rows so that it starts with the letter a and goes up to the letter h. It becomes its numerical equivalent to increase it.
3. And finally the formula is used to convert the number back to its equivalent letter.
By Andrea Ruiz, Consultant de BI/BA