Data generation : Generate set months

[akshatkumar87]

Hey guys,

Below is how my data looks like

id mo_id  c1 c2 c3

1 201401 3 4 5
1 201402 5 2 5
1 201403 1 5 1
1 201404 5 2 7
2 201401 4 2 5
2 201402 5 1 4
2 201403 4 1 4

What i am trying to do is generate null values for rest of the month for the year for ex : We have data till april for id 1 i want to generate equal grids for all the ids so in short i want 24 rows for all the ids 1 and 2.

Thanks in advance

[KaneG · Accepted solution]

Hi @akshatkumar87,

You can use the generate Rows tool to do this, but what I usually do is put a start and end field in to a text input, then use a Generate Rows tool to create all the months, append that to the ID's so you have all the records you want, then join that to the data to fill in the fields that there is data for. 

The reason that I use this method is because it is the same any time you want to do this kind of thing, regardless of the data, datatypes etc and you can clearly see that you have created all the missing fields.

[rahuls · Accepted solution]

I have attached an alteryx workflow file having the desired output. The tools used in the workflow are Generate Rows, Formula, Union and finally a Unique Tool.

Do take care of the data type matching in formula tool, manually configure fields in Union Tool and other coinfiguration properties while going through the workflow.

Hope this helps you.

[blitz45]

do you have the workflow or recreate so we could download to see how it works? 

[blitz45]

@KaneG do you have the workflow or recreate so we could download to see how it works?