Cross Tab questions

Hello All,

A have used the Cross tab and I have 2 questions regarding using that tool.

1. In the input of the Cross tab, the Values which are right now in the column names are in alphabetical order. Can I somehow preserve the original order of the values in the Column order, as well?

2. I had some special characteristics in the values like [ ] and % which are correctly visible until the using of the Cross tab. As that time until they are values there is no issue. But when I use the cross tab and from the values there will be the column name, I will get _ instead of the special characters.

Can you please help me how could I solve this issues?

Thank you.

Transformation

Accepted answers

OllieClarke

@bakaidora here's one approach at doing this

Cross-tab.yxmd

All comments

DataNath

Hey @bakaidora, I currently have a 'Work in Progress' macro to try and tackle this - restoring original order and names of fields that become affected by Cross-Tab, would you like to try it?

The top (A - After) anchor requires you to plug the data in when the names/orders are messed up. The bottom (B - Before) anchor you just need to plug in at some point beforehand when the field names and order are how you want them:

The underlying workflow here really works on:
1) Transposing your data to get the field names as they are

2) Using a RecordID to make the name of each field 1,2,3 etc so that when they're cross-tabbed, they come out in the correct order

3) Cross-tab your data as you wish - you'll end up with field names such as 1,2,3,4

4) You use a Dynamic Rename tool to look for these names (1,2,3,4....) and replace them with the original headers

I've also attached the demo workflow used in the screenshot above so you can see what's going on.

WIP.yxmc

Cross-tab fix macro demo.yxzp

ChrisTX

See these posts:

preserve the original order of the values in the Column order

CrossTab tool arranges output columns alphabetically - how to change column order field order

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Breakdown-one-column-into-lagged-columns/td-p/463010

column naming.... I will get _ instead of the special characters

How to handle special characters when applying cross tab to transpose field value to field name

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-handle-special-characters-when-applying-cross-tab-to/td-p/637

Module here

http://downloads.chaosreignswithin.com/community/KeepFieldNamesThroughCrossTab.yxmd

Can the real [Field Name] please stand up...

https://community.alteryx.com/t5/Engine-Works/Can-the-real-Field-Name-please-stand-up/ba-p/611088

Chris

OllieClarke

@bakaidora here's one approach at doing this

Cross-tab.yxmd

bakaidora

@OllieClarkethanks it was the perfect solution for me.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703