CrossTab changing headers

[jcardoso]

Hi,

the CrossTab tool is changing the header values when these have curve brackets as print screen attached!

How do we stop this behaviour? I need to match the same values later in the workflow,

Thank you,

 

[Jonathan-Sherman]

Hi @jcardoso,

This could be achieved by duplicating the column headers, unioning back and using a dynamic rename to avoid the issue of special characters being changed to underscores.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[afv2688 · Accepted solution]

Hello @jcardoso ,

Use the dynamic rename with the following formula:

Regex_Replace([_CurrentField_], '(.*)\_PPM\_(.*)?', '$1\(PPM\)$2')

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[ChBurger]

@Jonathan-Sherman I liked the idea of your solution, but I did not feel it was dynamic enough for my needs. Due to this I have increased the complexity a bit, but inevitably this will permit the use of primary fields (grouped by columns) as well as secondary fields and accounting for the fact that both segments will be sorted in the output after the cross-tab functionality has been completed.

In this case I am taking a subset of the initial data before it is transposed, obtaining a complete list of columns, then getting the data after it was transposed with the grouped columns and joining the two datasets together. The remaining items are the grouped by columns. We can then take those and sort Ascending and add a generated number starting at 1. Taking the secondary columns and adding a generated number starting at 999 then produces the secondary column sort. We then perform logic to add the primary value to the secondary column and sort overall. Finally, we dynamically replace the columns with the first row after the union of all data. I appreciate the starting point as it helped me get this completed rather quickly!