loop through a dataset and add new columns dynamically

Question

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

Claje · Accepted Answer

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!