Looping in workflow to create calculated columns

[Saras]

Hi,

I need to create 12 new columns in existing sheet based on Months, i have another column named XXX (Date field).

For example:

if the XXX column has the value "August 2016" then i need to populate the value 1 in all new column from JAN to AUG

if the XXX column has the value "September 2017" then i need to populate value 1 in all new column from JAN to DEC

if XXX has "September 2016" then data value 1 from JAN to SEP should br populated.

Please help me with this issue.

[JohnJPS]

HI Sara,

It seems like you could do this with a Formula tool that first defines a integer type variable "XXX_AS_INT" which converts "August 2016" to a number (e.g. 8 for August).  Then define all twelve of your new columns and populate them based on whether or not the number in "XXX_AS_INT" is less than or equal to the number indicated by that column's month

so...

the "JAN" column will have an expression IF XXX_AS_INT <= 1 THEN 1 ELSE 0 ENDIF

the "SEP" column will have an expression IF XXX_AS_INT <= 9 THEN 1 ELSE 0 ENDIF

the "DEC" column will have an expression IF XXX_AS_INT <= 12 THEN 1 ELSE 0 ENDIF

etc...

Hope that helps!

[pcatterson · Accepted solution]

I think this does what you need. 

[jdunkerley79]

Taking @pcatterson example but using a cross tab and some multifield formula

 

[PaulT]

I have a relatively low-tech way of doing it. If your date field is actually a string field you can basically parse out the year for each of your records. Then using a Formula tool you can create a new numeric field for the current year to identify records that go beyond the current 12 month calendar year. The forumla tool expression is:

ToNumber(DateTimeFormat(DateTimeToday(), "%Y"))

In that same Formula tool do another calculation to determine the records that fall within the current year and those that fall outside, then multiply by 12 (I choose to do this calculation in the existing Year field from your original data.

([Year Check]-[Current Year])*12

From here join a look up table with Month name and Month position to your data to get the month position and anywhere the Year from your original data is greater than 12 (from the prior calculation), set the Month Position value to 12. (See attached example). 

The last step would be to bring in another look up table that has a corresponding matrix like the one you described in your original statement.

Hope that helps!