Dynamic Field names when renaming

[Taki]

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.

[fmvizcaino · Accepted solution]

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

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

[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 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,

[Taki]

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,