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

    Columns order change in calculated field

    Vamsi Challa

      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:

      Col1Col2Col3Col4Col5
      AMP
      BHLMP
      DIMP
      EMP
      FMP

       

      My Requirement - 

      Col1Col2Col3Col4Col5
      PMA
      PMLHB
      PMID
      PME
      PMF
        • 1. Re: Columns order change in calculated field
          Aaron Dobbins

          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
            Vamsi Challa

            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
              Aaron Dobbins

              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.