3 Replies Latest reply: Feb 3, 2018 9:01 PM by Vishwarath Nagaraju RSS

    convert columns into rows in straight or pivot table

    nareshthavidishetty nareshthavidishetty

      Hello all,

       

      Table:

      LOAD * INLINE [

      Region, Country, Site, Value

          EMEA, UK, UK_Site1,100

          EMEA, UK, UK_Site2,200

          EMEA, Finland, Finland_Site1,300

          EMEA, Finland, Finland_Site2,400

          NA, Canada, Canada_Site1,100

          NA, Canada, Canada_Site2,500

          NA, USA, USA_Site2,100

          NA, USA, USA_Site4,600

          NA, USA, USA_Site3,700

          NA, USA, USA_Site1,800

       

      ];

      I have a data like above Inline table and my requirement is

      based on the selections I want to show the associated sites  along with selected Region and Country like below table[Region='EMEA',Country='USA'  and no selection on Sites]

       

      Temp_Dim

      Value

      EMEA1000
      UK300
      Site1100
      Site2200
        • Re: convert columns into rows in straight or pivot table
          Mohammed Mukram Ali

          Hi,

           

          Maybe like this.

           

          Table:
          LOAD * INLINE [
          Region, Country, Site, Value
              EMEA, UK, UK_Site1,100
              EMEA, UK, UK_Site2,200
              EMEA, Finland, Finland_Site1,300
              EMEA, Finland, Finland_Site2,400
              NA, Canada, Canada_Site1,100
              NA, Canada, Canada_Site2,500
              NA, USA, USA_Site2,100
              NA, USA, USA_Site4,600
              NA, USA, USA_Site3,70
              NA, USA, USA_Site1,800
          ];
          Temp_Data:
          LOAD
          Region as Temp_Dim,
          Sum(Value) as Values
          Resident Table
          Group by Region;
          LOAD
          Country as Temp_Dim,
          Sum(Value) as Values
          Resident Table
          Group by Country;
          LOAD
          Site as Temp_Dim,
          Sum(Value) as Values
          Resident Table
          Group by Site;
          DROP Table Table;
          

           

           

          290147.PNG

          • Re: convert columns into rows in straight or pivot table
            Vishwarath Nagaraju

            Try this:

             

             

             

            Region:

             

            LOAD * INLINE [ 

            Region, Country, Site, Value 

                EMEA, UK, UK_Site1,100 

                EMEA, UK, UK_Site2,200 

                EMEA, Finland, Finland_Site1,300 

                EMEA, Finland, Finland_Site2,400 

                NA, Canada, Canada_Site1,100 

                NA, Canada, Canada_Site2,500 

                NA, USA, USA_Site2,100 

                NA, USA, USA_Site4,600 

                NA, USA, USA_Site3,70 

                NA, USA, USA_Site1,800 

            ]; 

             

            Dim:

            LOAD * INLINE [

            Dim

            1

            2

            3

            ];

             

            Then using Straight table or Pivot table write the below dimension and expressions.

            Thanks to stalwar1 for this trick i learnt from.

             

            Dim: = Pick(Dim, Region, Country, Site)

             

            Expr: = Pick(Dim, Sum({1} Value), Sum(Total <Country> Value), Sum(Value))

             

            Then add your fields as filters and select accordingly.

             

            Capture.PNG