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

ETL Challenge !!

gnans19
11 - Bolide

I am trying convert my client data into our standard structure using a mapping file. If there is 1:1 mapping, the logic works well using Dynamic Rename tool. If I have the same source field mapped to multiple target fields, I cannot get through using Dynamic Rename tool.

 

PS: I can achieve the result using Transpose, Join and CrossTab. That's not an option. I have 2 Million rows and 300 columns. 

 

Attached workflow with sample data and expected result

 

image.png

15 REPLIES 15
Thableaus
17 - Castor
17 - Castor

Hi @gnans19 

 

The D1 and AA1 fields are not present in your Source. 

So you want to generate fields based on the mapping?

 

Cheers,

gnans19
11 - Bolide

@Thableaus .. Yes. Refer the expected output

jarrod
ACE Emeritus
ACE Emeritus

After the Mapping input, ad a formula with 1 as the value set to byte (or whatever type you want the new fields to be).

Then crosstab so that "Target" is used for the headers and "sum" (or "first" if it's text) the new field you created in the formula above.

add a filter tool with 'false' as the only value in the custom filter and connect the true path to a union tool

Connect the dynamic rename tool to the union as well.

 

clipboard_image_0.png

gnans19
11 - Bolide

@jarrod Can you attach the workflow?

jarrod
ACE Emeritus
ACE Emeritus

here you go. 

 

i don't know where the other data is coming from though for AA1 expected output field.

gnans19
11 - Bolide

@jarrod Also you missed the 2nd Mapping.  Dynamic Rename Tool considers only the first mapping for 'a'.

jarrod
ACE Emeritus
ACE Emeritus

oh, so you want to duplicate the "a" field automatically? for that, i would turn to some sort of batch macro where it inserts a formula for any fields that are "mapped" more than 1 time. 

gnans19
11 - Bolide

@jarrod Thanks for the workflow. Please look at my expected result.

 

AA1 should also pull value from 'a'

gnans19
11 - Bolide

@jarrod  yes. I am exploring batch macro option now.

Labels