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
Solved! Go to Solution.
Hi @gnans19
The D1 and AA1 fields are not present in your Source.
So you want to generate fields based on the mapping?
Cheers,
@Thableaus .. Yes. Refer the expected output
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.
@jarrod Can you attach the workflow?
@jarrod Also you missed the 2nd Mapping. Dynamic Rename Tool considers only the first mapping for 'a'.
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.
@jarrod Thanks for the workflow. Please look at my expected result.
AA1 should also pull value from 'a'
@jarrod yes. I am exploring batch macro option now.