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

MACRO - Column Name Mismatch

suby
11 - Bolide

I have 2 Macro of which the first one imports Multiple File sheet names and the second Import Multiple schema flat files Macros.

 

Importing File 1 & File 2 and each file has one Tab called F1.

 

The issue I'm facing is on File 1 the third column is Called STATUS and File 2 the Fourth column is called STATUS NEW

 

Expecting solution for

 

- Firstly how to Handle the STATUS & STATUS NEW Column name changes ( so that i have one column called status)
- Secondly on file 2 there where new column added will that be an Issue when we do joins further down the workflow.

 

Attached the Macro along with the Input Data and Sample workflow.

 

Thanks

4 REPLIES 4
JoaoLeiteV
10 - Fireball

Hello,

 

If you're going to only have "Status" and "Status New" you can use a formula to check if "Status" is null. If it is, get results from "Status New". Then, use a Select Tool to remove Status New.

 

JoaoLeiteV_0-1626372936454.png

 

Would this work for you? Also, additional columns will be appended to your results the same way the "TEST" column did.

suby
11 - Bolide

Thanks for your solution just to clarify on the Formula Field do i need to Select the existing STATUS column and then apply the formula or I have to create a new column Called STATUS.

 

I have hundreds of columns but there are quite few columns which are not aligned is this the only approach to handle this so if we want to change the multiple columns then is there a smart way of doing it.

 

Many Thanks

JoaoLeiteV
10 - Fireball

In the example I've made I used the already existing "STATUS" to update the values, if you have other columns with the same problem, you can do the same for each of them.

 

About best approach: If your data has aligned columns, you could unite them by Position, but as some files may have more columns, this would not be optimal.

 

I tried using another solution:

   1. Using a RecordID to number the lines.

   2. Transpose tool (Where Record ID is a Key Column) to change columns to rows.

   3. Formula to find everything that contains "Status" and change it to "Status".

   4. Crosstab to change data back into columns (RecordID is set as a Group Data, "Name" is header, "Value" are Values, Method is Concatenate)

 

JoaoLeiteV_0-1626374910658.png

 

This way, even if you have 2 or more STATUS with different names, they would all be considered. You still would need to do this formula for every column that needs to be readjusted.

 

Also, beware of columns that may be renamed wrongly, due to having a similar name.

 

suby
11 - Bolide

Hello Thank you so much for the solution and I'll stick to first solution and that makes it easy rather than adding too many tools on my workflow.

 

Thank you again Much appreciated.

Labels