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
Solved! Go to Solution.
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
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,
Perfect! That's great.