Sorting Fields based on original position from raw data

[caltang]

I’ve made some data manipulations with an analytic app thereby allowing end users the ability to select their fields of choice, apply a formulated % change to values, and to activate another chain app once they’re done. 

However, I am unable to sort the raw data that comes out of it to follow the original raw data. 

For example, 

Columns in original dataset:

A | B | C | D | E

Columns in current output:

E | C | A | B | D

The Expected: 

A | B | C | D | E

How may I do this? There can be some scenarios whereby some columns will be dropped after selections, so it can be like: 

Columns in original dataset:

A | B | C | D | E

Columns in current output:

E | C | A | B 

where D is dropped. 

The Expected:

A | B | C | E

where D is skipped as it was dropped. 

Thanks!

[rzdodson]

@caltang: built a small analytic app for you. This will give you the capacity to select which letters you wish to keep from the sample data set, and then output the original order based on an ascending sort of the key field created from RecordID (2) in the workflow.

[DataNath]

@caltang one of the CReW macros is the Field Sort tool whereby you can sort your fields in ascending or descending order. Can download and plug that or even reverse engineer it to just build the functionality out yourself. Credit to @MarqueeCrew and co!

http://www.chaosreignswithin.com/p/macros.html

Quick edit:

I think you'll have to take the concept (basically using the Field Info tool into a dummy Union to set the order) and create your own as I'm guessing your real data doesn't happen to come in in a straight ascending or descending order but could be fields starting with various letters that you just want kept in that original order.

[rzdodson]

@caltang I misunderstood your original request. I had thought that your data was stacked vertically. Can confirm that the solution that @DataNath provided would be what you are looking for unless you wanted to create a Transpose/RecordID combo for your build field key.

[caltang]

Thanks @rzdodson & @DataNath - let me try a different way and report back here.

[caltang]

Hey guys @rzdodson @DataNath 

I actually experimented further, and the results show that the columns are sorted correctly. The rows too. 

Not sure why end users were complaining initially, but I think it is because they selected many different columns and were confused on the sort order since some of them are quite identifical. 

But to your point on Transpose tool & then Cross-Tab once done, I am not sure if it's feasible because if I have 9,000 rows with 8,344 columns, that's effectively 75,096,000 rows - very heavy load!

Thanks to both of you, by the way, for commenting & giving ideas!

[rzdodson]

@caltang no worries. Glad when the original plan comes together ;)

I personally would not go with the Cross-Tab/Dynamic Rename option until we remove ~8000 of those columns that aren't needed. Kidding aside, glad it sparked some creative solutions for your future workflows.