Change field name based on value in filename?

[hellyars]

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.?   

[hellyars]

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

[tcroberts]

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]

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 · Accepted solution]

Try this.

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

[hellyars]

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]

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]

@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 · Accepted solution]

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]

Ugh, I was seeing THEN but thinking ELSEIF