Dynamically create 12 variables (Jan to Dec)

[Rev80]

Hi all,

Hoping someone can help here. Thank you in advance.

I am looking for a way to dynamically create M01, M02, M03, M04, M05, M06, M07, M08, M09, M10, M11, and M12 based on the input table. The input table changes – sometimes it may have M1, M2 and sometimes it may have M3 and M4, as a few examples.

I want to create an automated process that creates these twelve variables. If the column (M01 to M12) exists in the input file then use it  - if not, then create it and simply set the values to 0.

Example 1:
Input data contains: M01 and M02
Process: takes M01 and M02 from input and creates M03 through to M12 and sets them with 0 values

Example 2:
Input data contains: M03 and M04
Process: takes M03 and M04 from input and creates M01 and M02 and also creates M05 through to M12 and sets them with 0 values
 
Example 3:
Input data contains: M09, M10, M11 and M12
Process: takes M09, M10, M11, and M12 from input and creates M01 through to M08 and sets them with 0 values

Regards,

 
Rev

[MarqueeCrew · Accepted solution]

@Rev80 

If you UNION a text input file that has the required fields (e.g. M01 to M12) and include your data, then you'll always have the 12 months.

Cheers,

Mark

[Thableaus]

Hi @Rev80 

I'd try to use Join Multiple (see if it works for you)

- 1st Text Input tool is your data with Month Columns

- 2nd Text Input Tool is made up of all of Month Columns with 0 values.

- You then join multiple the two datasets by Record Position. Make sure your "Unknown" field is checked.

- Use Dynamic Select to get rid of duplicate fields from 2nd dataset. They are easy to identify by the "Input_#" prefix that is added to them.

I used the formula: !StartsWith([Name], "Input_#")

- Use Data Cleansing to transform Null values to 0.

Just a different point of view, tell me if it works out for you.

Cheers,

[Rev80]

Perfect! That's great.