Change field name based on value in filename?

Accepted answers

All comments

Quick Links

This months top contributors

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

Tips and Tricks

Preparation

Expression

tcroberts

Try this.

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

QuestionSample.yxmd

tcroberts

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.

QuestionSample.yxmd

tcroberts

I think your best bet here is to use information from the Directory Tool to grab the year.

I've attached a sample workflow that uses this method:

Spoiler

What I've done is use Directory Tool -> Dynamic input to read my data in (you'll likely have to make the directory tool look for more specific files, or add a filtering step before the Dynamic input/parse tool.

I then create a target column, with the name current year, so I can match to the transposed columns of the input file. Once I make this match, I use the multi-row formula to rename them. I've created a duplicate of the Name column here, but I don't think it's necessary in general, I just wanted to show that the numbers were generated correctly.

For this it is important that the "Values for Rows that don't Exist" is set to NULL, since I use a Null check to set Prior Year correctly.

After removing unnecessary information, I CrossTab back into the original format.

Let me know if you have any questions, or this workflow doesn't work for you for whatever reason.

Cheers!

usefilenameasvalue.yxmd

hellyars

I can kind of follow what you are trying to do. But, I think there might be another challenge. The PY, CY, Yr1... fields are not available at source input. I have to parse to extract them.

Once parsed, the data looks like this.

Individually, I simply use the select tool to rename PY, CY, Yr1... to the correct year. But, I am trying to avoid building a long lengthy formula that has to be updated every year.

	14	15	16	17	18	19	20	21	22	23
fy15_filename	10	15	20	25	30	35	40
fy16_filename		10	15	20	25	30	35	40
fy17_filename			10	15	20	25	30	35	40
fy18_filename				10	15	20	25	30	35	40

fy15_filename

fy16_filename

fy17_filename

fy18_filename

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703