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

Change field name based on value in filename?

hellyars
13 - Pulsar

Okay, I have an annual file. 

 

The format of the filename is "fy##-name.abc"

 

Within each file year's are identified as Prior Year, Current Year, Year One, Year Two, Year Three, Year Four, Year Five.  

 

How can I build a formula that dynamically uses the two ## digits in the file name to convert Prior Year to ## - 2, Current Year to ## -1, Year One to ##, Year Two to ## + 1, etc.?   

18 REPLIES 18
hellyars
13 - Pulsar

The filename is no longer relevant.  Now its just an issue how can I use the Calendar Year field to solve the question. 

tcroberts
12 - Quasar

I've attached an updated workflow.

 

Now you'll want to apply the same steps, Transpose -> Multi-Row Formula -> CrossTab, grouping by Calendar Year instead of the RegEx output.

hellyars
13 - Pulsar

Try this.  Use this input.  I am only worried about how to translate Prior Year, Current Year... to a year based on the Calendar Year.  

tcroberts
12 - Quasar

Try this.

 

Group by CY, ProjectName and ProjectID in all the steps mentioned above.

 

hellyars
13 - Pulsar

It worked.  Thanks for your patience.  I am not familiar with the Multi-Row Formula tool.  

 

You first create a NewColName and and if CY2 matches BY then BY else Name.  Got it.

 

You set the value for rows that don't exist to Null.  Okay.  

 

The expression is looking for a IsNull value in NewColName.  But, what is the generating the Null.  How does it know to use the number set in the prior Formula?

 

 

 

tcroberts
12 - Quasar

The MultiRow Tool goes through the dataset top down, row-by-row. Since I've set the value for rows that don't exist to null, the first row (PY for CY15) will return a null, since there is no row before that.

 

In fact, since we are grouping by CY, ProjectName, and ProjectID, the first row (meaning the PY for that CY, ProjectName, and ProjectID) will always trigger the IfNull([Row-1:NewColName]), since there is no value before the first row, thus it gets set to Null.

 

You can think of it as the MultiRow Formula Tool breaking the dataset down into multiple datasets, each with the same combination of (CY, ProjectName and Project ID) At the end, it would union them all back together.

 

I'm doing this as a workaround, because we only have CY, so if the IfNull triggers, then instead of doing the normal "[Row-1:NewColName] + 1", we instead do [Row+1:NewColName] + 1, meaning that we look at the next row (which is always CY, the only one with a numeric value), and subtract 1 instead of adding one.

 

If the null check does not go off, that means that there is a row before it, so it is safe to just add 1. Since the PY has already been set to CY-1, adding back 1 will just give us CY again, thus not breaking the CY name.

 

Does this explanation help clear things up? If not let me know what's getting you stuck.

 

Cheers!

hellyars
13 - Pulsar

@tcroberts

 

I had to make a slight mod.   Yr1 not CY2 should be the actual reference point.  So, YR1 should = CY.   

 

IF IsNull([Row-1:NewColName])
THEN ToNumber([Row+2:NewColName]) - 2
THEN ToNumber([Row+1:NewColName]) - 1
ELSE ToNumber([Row-1:NewColName]) + 1
ENDIF

I adjusted the Formula to set the number on YR1 = BY.  But, I am missing something in the expression.

tcroberts
12 - Quasar

In Alteryx: IF statements always need to be of the form: 

 

IF c

THEN x

ELSE x2

ENDIF

 

In your case, you have two THEN clauses. If you want this you'll need to do ELSE IF.

 

IF IsNull([Row-1:NewColName])
THEN ToNumber([Row+2:NewColName]) - 2
ELSE ToNumber([Row-1:NewColName]) + 1
ENDIF

 

Just make sure you change the "Num Rows" configuration to "2" instead of 1, so that the MultiRow Tool can see further ahead.

hellyars
13 - Pulsar

Ugh, I was seeing THEN but thinking ELSEIF 

Labels