3 Replies Latest reply on Sep 7, 2018 7:19 AM by Aaron Dobbins

# Columns order change in calculated field

Hi Forum,

My data is like in below.

Method tried - I Merged all 5 columns in calculated field and removed nulls and perfromed per and tried to  reverse the column order to look like below required one. But was not successful. Please help me on this.

My Data currently:

 Col1 Col2 Col3 Col4 Col5 A M P B H L M P D I M P E M P F M P

My Requirement -

 Col1 Col2 Col3 Col4 Col5 P M A P M L H B P M I D P M E P M F
• ###### 1. Re: Columns order change in calculated field

Hi Vamsi,

How many possible values exist for each column, and how many columns?  You will have to check each row to see if a value exists across all columns:

Has P

[Col1] = "P" OR [Col2] = "P" OR [Col3] = "P" OR [Col4] = "P" OR [Col5] = "P"

Has M

[Col1] = "M" OR [Col2] = "M" OR [Col3] = "M" OR [Col4] = "M" OR [Col5] = "M"

...

do this for each possible value across all possible columns.  could be tedious if there are many possible values and many possible columns.  If it is the five columns above with the values above it is not too burdensome.

Then define new columns 1-5 and check what exists in each row.

Col1 Sorted

IF [Has P] THEN "P"

ELSEIF [Has M] THEN "M"

...

END

do one ELSEIF for each possible value.

In the Col2 Sorted through Col5 sorted you need to check if a value has already been used in a previous column.

Col2 Sorted

IF [Col1 Sorted] = "P" THEN

IF [Has M] THEN "M"

...

END

ELSEIF [Col1 Sorted] = "M" THEN

IF [Has A] THEN "A"

...

END

END

The key is knowing what order the values should be sorted and checking for that value.

Then use those sorted columns in your output.

• ###### 2. Re: Columns order change in calculated field

Thanks Aaron for replying. I am having multiple different values approx 50. This is not limited to above mentioned number of columns. I am having like more than 10 columns with 50 different values repeated.

• ###### 3. Re: Columns order change in calculated field

Hi Vamsi,

You probably don't want to manually check each one then.  Maybe you could create a "rank" reference sheet that has the letter, and where it should be ranked, then use Tableau Prep to pivot the columns into rows, join to the rank reference sheet, then sort the data.