ETL Challenge !!

[gnans19]

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

[Thableaus]

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]

@Thableaus .. Yes. Refer the expected output

[jarrod]

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.

[gnans19]

@jarrod Can you attach the workflow?

[jarrod]

here you go. 

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

[gnans19]

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

[jarrod]

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]

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

AA1 should also pull value from 'a'

[gnans19]

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