Dynamic Field names when renaming

Hi all:

I am new to Alteryx and would appreciate any guidance on the following: I am looking to compare 2 excels where I will have columns consisting of months like Jan and Feb and March and so on. And lets say 1 row for cost and another row for profit. I need to compare if the Jan cost matches from the current to prior Excel so I would also add a column for the match. Since the column can roll off meaning column 1 maybe Jan. this month but next month, column 1 maybe Feb (rolling year). As a result, I would like to make the column name like "Curr_jan" and "Prev_jan" where the Jan can be dynamic as it can change. Same when I add the column for the compare results like "jan_match?". Thank you in advance for your help.

Common Use Cases

Accepted answers

fmvizcaino

Hi @Taki ,

Would you be able to provide a small sample of your datasets and a desired output?

I understood your problem but to build an example for you, it is better to have a dataset similar to yours.

Also, how do I know which month to insert first in your rolling month columns? based on the filename?

Best,

Fernando Vizcaino

estherb47

Hi @Taki

Sounds like you are joining one data set with another. In this example, I'd transpose the data from both files so that months are listed down in rows rather than across in columns.

You can then use a join tool, matching on whichever field represents your month, and anything else that needs to match (like the column listing "cost" or "profit"). The order in which the months appear in the data doesn't matter -- the spelling and capitalization do matter.

The join tool will let you rename the fields with "Curr" and "Prev" as headers. And then a formula tool can compare (e.g., a new field called "Month_match", which is IIF([Curr_month]=[prev_month],"y","n")

You'll have a new column that lists whether a month matches to the month in a previous year.

I've attached an example with some dummy data. Let me know if this helps. The format outputted isn't great for further analysis, but will let you see the comparisons underneath.

Cheers!
Esther

TakiSolution6-24.yxmd

All comments

fmvizcaino

Hi @Taki ,

Would you be able to provide a small sample of your datasets and a desired output?

I understood your problem but to build an example for you, it is better to have a dataset similar to yours.

Also, how do I know which month to insert first in your rolling month columns? based on the filename?

Best,

Fernando Vizcaino

estherb47

Hi @Taki

Cheers!
Esther

TakiSolution6-24.yxmd

Taki

The examples are just made up where I have last month and current month with a 12 month role of (meaning only 12 month of data so in Feb. the first column will be Feb .... Jan (next year):

Jan Feb March.....

Cost 20.00 40

Profit 40.00 60

I think the solution offer from another developer would work which is to convert the columns to rows and rows to column. If you have an alternative, I would like to know about it as well as Alteryx is new to me. Thank you again

Taki

The solution offered by Ester does work but I was wondering if it is possible to that instead of the match being in the row that it could be in a column instead. Meaning:

Jan 2019 Jan 2020 Match? Feb 2019 Feb 2020 Match?

Costs 20 30 N 20 20 Y

profit 40 40 Y 25 40 N

Taki

Hi Ester

Thank you for your solution and it did work but I was wondering if it is possible instead of the match being in the row that it could be in a column instead. Meaning:

Jan 2019 Jan 2020 Match? Feb 2019 Feb 2020 Match?

Costs 20 30 N 20 20 Y

profit 40 40 Y 25 40 N

Taki

Hi Fernando

Instead of the rolling month, I have another example where it is looking to compare prior quarter to this quarter. For example: prior quarter will have Jan, Feb and march and this quarter will have Jan, Feb, Mar, Apr, May and Jun.. The comparison will be make where the prior month will also have data which is Jan, Feb and March. for the April, May and June which only exist in the current month, it can be ignore or if match is no is also fine.

current file Prior file

Jan Feb March.....April Jan Feb Mar

Cost 20.00 40 20 20.00 45 20

Profit 40.00 60 35 45.00 60 20

I would like the result to show:

Curr_jan prior_Jan Match? Curr_Feb Prior Feb Match? Curr_Mar Prior Mar Match?

Cost 20.00 20.00 Y 40 45 N 20 20 Y

Profit 40.00 45.00 N 60 60 Y 35 20 N

Thanks,

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703