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

dynamically create columns via macro

DennyChan
8 - Asteroid

hello

 

I am new to the atleryx macro writing, and hope you can help.

 

The scenario i have is that i have different files that have varying number of columns, these columns will always have the same columns output order.

what i would like to incorporate into a macro is dynamic column import

 

I can code the cleansing and ordering, what i need is how to code the connection from a file to the macro

 

so the fruits below are different columns, the macro is assumed as the same macro in all examples

 

_______________________________________________________________

example 1

 

APPLE PEARS BANANA ORANGES

 

these columns are parsed into the macro and outcome is always

 

APPLE PEARS GRAPES BANANA PLUMS ORANGES

 

_______________________________________________________________

example 2

PEARS BANANA 

 

these columns are parsed into the macro and outcome is always

 

APPLE PEARS GRAPES BANANA PLUMS ORANGES

 

_______________________________________________________________

example 3

PEARS BANANA PLUMS ORANGES

 

these columns are parsed into the macro and outcome is always

 

APPLE PEARS GRAPES BANANA PLUMS ORANGES

 

i would like to know how to code the below macro input, to accept any number of columns.(not limited to the column in the tool)

 

denny_chan_0-1572015232011.png

 

 

hope that makes sense

 

 

 

thanks in advance

 

Denny

 

 

 

6 REPLIES 6
T_Willins
14 - Magnetar
14 - Magnetar

Hi @DennyChan,

 

It may be easier to prep the data prior to inputting it into the macro.  I have used a text input as a master template to conform other inputs to include needed columns.

 

Master template 2.JPG

 

Treyson
13 - Pulsar
13 - Pulsar

Are you saying that you have a certain number of formats that you know will have and that the macro needs to process differently for each format? 

 

If so, I am thinking you might need to have two macros. The first would bucket each input file into whatever format category it belongs to. Then the second macro would be a batch macro that processes based on their category and then in the output of that macro you have some sort of master with all the column names that allows all of the outputs to be the same, similar to what @T_Willins described.

Treyson Marks
Senior Analytics Engineer
Dazzerman
11 - Bolide

Hi @DennyChan ,

 

I have attached my solution to your problem, along with the test data I used, which I stored in a C:\Test folder.

 

This solution incorporates ideas mentioned by @T_Willins  and @Treyson, plus has the following benefits :

1. If you include "Filename" as one of the input columns in the Wrapper workflow, you get a column that indicates which file each row comes from

2. If you specify duplicate column names, only the first instance of the column will be reproduced

3. If you specify a column name that is not in any of the files, the column will be included but will only contain nulls

4. You can change what columns get extracted whenever you want without changing the macro.

 

I hope this is useful and helps you achieve what you are trying to do.

 

Let me know if there are any issues?

Dazzerman
11 - Bolide

I tried to re-load my macro and discovered that there is a known bug that has cropped up within it.

 

I have taken the advised steps to resolve the problem, so uploading the corrected version here.

DennyChan
8 - Asteroid

thanks Dazzerman work like a charm, could you help me understand the macro 

 

would it be possible to explain what each step is doing

also how would i change the input to my working files

much appreciated 

 

Denny

 

Dazzerman
11 - Bolide

Hi @DennyChan,

 

That's great that the macro does what you need!

 

The bottom data channel takes the column names from the B Input, supplied in rows, and transforms them into columns in the correct order.

 

This data is then fed into the Union Tool, which is configured to use the #1 data channel input as the dominant column order, which then re-orders the columns from the #2 data channel as required.

 

The Dynamic Rename tool sets the value of the Description meta data for each column to be named the same as the 'required' columns names specified in the B Input.

 

The Dynamic Select then magically selects only columns where their Description meta data is equal to its column name.  Thus removing all the columns that you don't want.

 

Finally the first row of the data (from the B Input) is removed for output back to your workflow that calls the macro.

 

It may be worth removing the Select tool, if the data in your required columns could be String format as well as numeric.

 

I hope this helps.

Labels