community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

How to Dynamic Rename a header based on an input value

Meteor

We plan to migrate data from a system to another system and do parallel run. Data from various sources is loaded into the existing system database DB1 and into the new system database DB2. From end-users' perspective, data from both databases should be same. There is no guarantee, for each table x, the record order in DB1 will be same as in DB2.

 

Given a list of table names, we like to do the following (pseudo code/workflow):

for each table x in a given list
 - retrieve data from the table x in DB1 => S1
 - retrieve data from the table x in DB2 => S2
end for each
compare the data (column by column) of S1 and S2 & report which column & values don't match

 

Procedure to retrieve data from table x

 - retrieve data from table x
 - rename table x primary key column to "PRIME_ID"
 - transpose table x to key-value pairs with SCHEMA_TABLE, PRIMARY_COL, and PRIME_ID as key fields
 - output/append the transposed data to Alteryx database S

 

Challenge:
- How can I dynamic rename the primary key header to PRIME_ID given a primary key header value for each table (see the sample below)? 

 

 

2018-01-26 15_40_20-sample-list-of-tables.png

 

 

 

 

 

 

 

 

Sample tables2018-01-26 15_41_18-sample-table-n-data-orig-x-desired-output.pngLeft: Original tables; Right: Desired transformed tables

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I suspect I have to use batch processing with macros to process each table. I am stuck in how to dynamic rename a header based on another dynamic input data (to determine which header to replace for each table. I hope someone can advise on the part I am stuck in.

 

If you have better idea on comparing tables from two databases, really appreciate if you can share with audience here.

 

Thanks.

Meteor

Here I attached the sample data Excel file.

Labels