Should I use a macro to solve this problem and can anyone provide an example workflow?

[ISHALL68]

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

[sskillen]

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!

[MRod · Accepted solution]

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

[ISHALL68]

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

[danilang]

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

[MRod]

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