Enter empty columns based on current month

[murthybhs]

I have a project where I have to accomplish this:

I have a PIVOT table with months in the columns as below:

I want to create empty columns until the most recent month that ended. For example, since the data I have is until Jan_2021 and today's date is 4/15/2021, I want two empty columns for Feb_2021 and Mar_2021. 

In short, I want empty columns for all the months until the most recent month that ended.

[apathetichell]

This isn't a one tool solution - (if you want to automate - there is the cheapo solution below) - so my questions are:

1) Is this something you are going to be repeating on a regular basis.

2) Is there potential data for those columns?

Here's how I see it - you have columns with date like features. You pivot the  columns into rows - date/time the name field (after replacing the "_") and then take the max/min using a summarize tool. Then you can generate dates for the months in between using generate row with your min as your initiation, your max in your condition and your formula with datetimeadd([rowcount],1,"months"] - pivot those and dynamic rename so you now have field names. Then union in the rows with your original data source - voila.

Obviously depending upon the answers to 1 and 2 and how many months you are doing this for - really determine if you want to do that - or just add two new blank columns in formula.

[Maskell_Rascal · Accepted solution]

Hi @murthybhs 

Here is a solution with a dynamic approach. It will always generate empty columns for anything missing based on date workflow is ran, and the minimum date found in your headers. 

I've attached the workflow for you to try out. 

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

[CatheyH]

Hi,

Some assumptions:

You want to populate empty columns for any missing month from the earliest data point

ie if your starting data set is Sep_2020, Nov_2020, Dec_2020, Jan_2020 then you'd want to include Oct 20, Feb 20 & Mar 20

You want zeroes rather than empty columns

Dates always be formatted as MMM_YYYY

Not totally pretty but I think it does what you need

If it solves, please mark as completed, although I can see the solution above is pretty much identical 🙂

Cathey

[murthybhs]

Thank you for the reply. It looks like your workflow is multiplying all the values in the months by 3. 

[Maskell_Rascal · Accepted solution]

Hi @murthybhs 

Sorry about that. I forgot to add an additional join field to the Join Tool. If you add a second join on the field Company, it will solve the duplication issue. 

Thanks!

Phil