Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Looping in workflow to create calculated columns

Saras
5 - Atom

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.

4 REPLIES 4
JohnJPS
15 - Aurora

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
11 - Bolide

I think this does what you need. 

jdunkerley79
ACE Emeritus
ACE Emeritus

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

 

 2016-06-21_18-01-24.jpg

PaulT
Alteryx Alumni (Retired)

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!

 

Labels