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 dateRecord IDValue 110/01/2019221020/02/2019222030/04/2019222103/07/2019221502/01/2019311121/01/201931930/01/201931704/07/20193122 I want however to end up with a table that presents that data as follows: DateRecord IDVALUE 131/01/2019221031/01/201931728/02/2019222028/02/201931731/03/2019222131/03/201931730/04/2019222130/04/201931731/05/2019222131/05/201931730/06/2019222130/06/2019317 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

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

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

Topic of Interest

Accepted answers

MRod

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

Solution.yxmd

All comments

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

HI @ISHALL68,

Good luck!

-Mark

Solution.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703