Should I use a macro to solve this problem and can anyone provide an example workflow?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi - I'm an inexperienced user of Alteryx and looking to solve a problem related to creating a data table from existing data to show monthly values.
I have a table containing the following
Date of Run
Agreement ID
Variable 1 Value
I want to report month end values for each agreement for the variable, however not all records have a value that will change each month, so I could have a table which looks like this:
Run date | Record ID | Value 1 |
10/01/2019 | 22 | 10 |
20/02/2019 | 22 | 20 |
30/04/2019 | 22 | 21 |
03/07/2019 | 22 | 15 |
02/01/2019 | 31 | 11 |
21/01/2019 | 31 | 9 |
30/01/2019 | 31 | 7 |
04/07/2019 | 31 | 22 |
I want however to end up with a table that presents that data as follows:
Date | Record ID | VALUE 1 |
31/01/2019 | 22 | 10 |
31/01/2019 | 31 | 7 |
28/02/2019 | 22 | 20 |
28/02/2019 | 31 | 7 |
31/03/2019 | 22 | 21 |
31/03/2019 | 31 | 7 |
30/04/2019 | 22 | 21 |
30/04/2019 | 31 | 7 |
31/05/2019 | 22 | 21 |
31/05/2019 | 31 | 7 |
30/06/2019 | 22 | 21 |
30/06/2019 | 31 | 7 |
So the reporting date will always be the last day of the month
I can do this manually by creating (in this example) 6 queries which select all records with a run date before the relevant reporting date, and sorting so that where there are 2 or more changes in the month, the 1st record is always the latest. Using Unique for Record ID I can then deliver 6 outputs which I can then combine into a single table (at the moment in Excel)
I figure there has to be a way of running it as a macro and delivering a table with columns for reporting date, Record ID and each variable
There are actually 20 variables which is why the output table is arranged as it is, and I'm intending to show each column as a different variable
Can anyone provide me a sample workflow to show how the macro would work ? or a better way of doing it
Thanks
Solved! Go to Solution.
- Labels:
- Topic of Interest
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The Generate Rows tutorial is very near what you are trying to do I believe (the sample workflows at the end of the article are particularly good, imo).
https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-Generate-Rows/ta-p/31368
Short of an exact solution for you, but hope that helps!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
HI @ISHALL68,
Welcome to Alteryx! Please see the attached sample workflow. I was able to use the Generate Rows tool to create the additional months when a Record ID did not change. Now that you have a working workflow, I would recommend you convert to a macro.
Good luck!
-Mark
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Mark,
I though this had worked, but when I put the real life data into it, I had problems so have revisited it and when I ran it with the data you used, I still get too many lines of data.
For each record ID in the example, there should only be 1 month end record, so I should end up with 12 lines of data, not 20
Any thoughts on how I would fix that aspect of the workflow ?
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @ISHALL68
Here's a workflow that builds the output you're looking for
It first converts the dates to Alteryx format and finds the end of month(EOM) for each date. Then the top branch takes the last value for each month and record ID. The middle branch pulls out the unique IDs and the bottom branch builds a list of all the EOMs between the start and end. The IDs and EOMs are cross joined to build a list of all the combinations of ID and EOM in the range. This is joined to the input data to fill in the values that we know. The Union tool combines the values that we know with those we need to calculate into one list. After sorting by ID and then EOM, the Multi-row tools fills in the values that are missing from the previous row. A final sort and you get this
Note that the process gives the entire month range in the input data, including Jul. If you don't want Jul in your output then add a filter at the end of the process to filter out the current month. Another point is that the value for Mar, ID=22 is actually 20 as opposed to 21 as your showed in your sample output.
Dan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @ISHALL68,
Please see the updated solution attached (illustration below)
i added in a Multi-Row formula tool to calculate the number of months between any changes (Months_Active).
I was then able to configure the Generate Rows tool to create the additional months. This workflow now produces the 14 rows that you requested (2 rows include a July 2019 date).
Thanks,
Mark
