1 Reply Latest reply: Dec 22, 2016 12:43 PM by Manish Kachhia RSS

    Making a table out of a single column

    james eckstein

      I have a table that has one column that is in this format

      Header 1

      Dimension1

      Dimension2

      Dimension3

      Dimension4
      Dimension5
      Metric_Value_1
      Metric_Value_2
      Metric_Value_3
      Metric_Value_4
      Metric_Value_5

      Dimension6

      Dimension7
      Dimension8
      Dimension9
      Dimension10
      Metric_Value_6
      Metric_Value_7
      Metric_Value_8
      Metric_Value_9
      Metric_Value_10

       

      5 dimension followed by 5 metrics I want the table to be transformed into a table that looks like this

       

      AgeCityStateCountryZipViewsLikesUpsDownsOvers
      Dimension1Dimension2Dimension3Dimension4Dimension5Metric_Value_1Metric_Value_2Metric_Value_3Metric_Value_4Metric_Value_5
      Dimension6Dimension7Dimension8Dimension9Dimension10Metric_Value_5Metric_Value_6Metric_Value_7Metric_Value_8Metric_Value_9

       

      The idea here is that there are always 5 dimension followed by 5 metrics associated with those dimension

       

       

      ***There is an unknown amount of rows in the column but it will be a multiple of 10

       

      Let me know if any other information is needed.

       

      Thanks in advance.

        • Re: Making a table out of a single column
          Manish Kachhia
          Temp:
          Load 
            Mod(RowNo(),10) as Key,
            1 as ID,
            Header
          Inline
          [
            Header
            Dimension1
            Dimension2
            Dimension3
            Dimension4
            Dimension5
            Metric_Value_1
            Metric_Value_2
            Metric_Value_3
            Metric_Value_4
            Metric_Value_5
            Dimension6
            Dimension7
            Dimension8
            Dimension9
            Dimension10
            Metric_Value_6
            Metric_Value_7
            Metric_Value_8
            Metric_Value_9
            Metric_Value_10
          ];
          
          
          Data:
          Load RowNo() as Link, Header as Age Resident Temp Where Key = 1;
          Left Join (Data)
          Load RowNo() as Link, Header as City Resident Temp Where Key = 2;
          Left Join (Data)
          Load RowNo() as Link, Header as State Resident Temp Where Key = 3;
          Left Join (Data)
          Load RowNo() as Link, Header as Country Resident Temp Where Key = 4;
          Left Join (Data)
          Load RowNo() as Link, Header as Zip Resident Temp Where Key = 5;
          Left Join (Data)
          Load RowNo() as Link, Header as Views Resident Temp Where Key = 6;
          Left Join (Data)
          Load RowNo() as Link, Header as Likes Resident Temp Where Key = 7;
          Left Join (Data)
          Load RowNo() as Link, Header as Ups Resident Temp Where Key = 8;
          Left Join (Data)
          Load RowNo() as Link, Header as Downs Resident Temp Where Key = 9;
          Left Join (Data)
          Load RowNo() as Link, Header as Overs Resident Temp Where Key = 0;
          
          
          Drop Table Temp;
          Drop Field Link;