Dynamically ordering columns

Question

Hi Community,

I have the following sample data:

IDTermValueCountryA1Y1UKA3M2UKA2Y3UKA2M4UKA3Y5UKB1Y6FRB3M7FRB2M8FRB3Y9FR

I use a "Cross Tab" to create columns from the values found in column "Term" (note that 1Y is 1 year, 2M is 2 months, etc). I populate the columns with the values from column "Value". Thus I end up with:

IDCountry1Y2M2Y3M3YBFR68 79AUK14325

I would like the table to show the columns in the following order:

ID2M3M1Y2Y3YCountryB876 9FRA42135UK

Thus the "Term" columns are ordered from shortest (2 month) to longest (3 year). And column "Country" is the last column. I can use a "Select" to set the order of the columns of course. But is there a way to do this dynamically? Thus if my data suddenly contains terms "1M" or "5Y" or whatever, it automatically allows for these new terms.

I can use a formula to map the terms to months: for example "2M" maps to 2, "3M" maps to 3, "1Y" maps to 12, ... and so on. But then what?

Thanks.

binu_acs · Accepted Answer

@RVDL one way of doing this by converting the year into months

Gaurav_Dhama_ · Accepted Answer

Cross Tab places the columns in Ascending order, so easiest way that I work with, is by placing a number order before the column headers and the later drop it. Attaching a workflow to demonstrate the same. The second text input allows you to set the level by which you want to reorder your columns. Change the order in that and your columns will reorder automatically.