Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

loop through a dataset and add new columns dynamically

SrikanthB
5 - Atom

requirement.JPG

I have an initial dataset where all a table with all unique columns names is listed. I have a list of tables that I need to check for the presence of these columns (like table_name_1, table_name_2, etc).

 

To achieve the target dataset, the way I would do in Python is:

1. create a list of table names (4 in this example)

 

2). loop through this list

2.1) retrieve a list of columns for the current table (from database) and create a list

2.2) on top of the initial dataset (say initial_df), create a column with name table_name_1 (the value of the table name in the list we are iterating) and default it's value to "N".

2.2) loop through the initial dataset (initial_df) and check if the column is present in list created in step (2.1) and then if present then initial_df[table_name_1] = 'Y'

 

There may be better ways in python itself :), but if I want to achieve something similar in Alteryx designer, how do I approach it? I'm trying some batch macro way but nothing is working so far. I'm contemplating on using python itself with Alteryx. Any better approaches from Alteryx will help me.

 

Thanks,

Srikanth

 

2 REPLIES 2
Claje
14 - Magnetar

Hi,

 

I've attached a quick example of how to do this.  There are simpler ways to accomplish this but they are less robust (EG if a column doesn't exist on any table, you wouldn't see that).  This should take care of those situations.

 

Basically we use "Append Fields" to create a list of all tables and expected columns, and then take advantage of the Join tool, which outputs missing records, to create a combined dataset.  Finally we use the "Cross Tab" tool to swap the data to the expected format.

 

Hope this helps!

SrikanthB
5 - Atom

Thanks a lot Claje. The speed at which you responded was awesome  :). The example works the way I need. I will try this out today and let you. Appretiate again for the fastest response I ever got.

 

Thanks,

Srikanth

 

 

Labels