2 Replies Latest reply: Oct 23, 2014 2:29 PM by Jonathan Poole RSS

    Need Help in moving column to row

      Is it possible to convert Row data to single column in Qlik Sense?

       

      Current format:

      BP IDYearQuarterSales
      1000120141100
      1000120142110
      1000120143120
      1000120144130
      100012013390

       

      Desired format:

      BP ID2013-Q42014-Q12014-Q22014-Q32014-Q4
      1000190100110120

      130

       

      BTW, desired format is possible via Pivot Table extension, but I need desired format for extract data to excel (if I extract via Pivot extension, I am still getting current format)

       

      Thanks for your help.

        • Re: Need Help in moving column to row
          jagan mohan rao appala

          Hi,

           

          Then you have to write separate expressions for each quarter

           

          Script:

          LOAD

          *,

          Year & '-' & 'Q' & Quarter AS YearQuarter;

          LOAD

          *,

          QuarterStart(MakeDate(Year, Quarter*3)) AS Date

          FROM DataSource;

           

          Dimension: BP ID

          Expression 1.

          1. Caption - Only({<Date={'$(=QuarterStart(Max(Date), -4))'}>} YearQuarter)

          Expression: Sum({<Date={'$(=QuarterStart(Max(Date), -4))'}>} Sales)

           

          Expression 2.

          Caption - Only({<Date={'$(=QuarterStart(Max(Date), -3))'}>} YearQuarter)

          Expression: Sum({<Date={'$(=QuarterStart(Max(Date), -3))'}>} Sales)

           

          Expression 3.

          Caption - Only({<Date={'$(=QuarterStart(Max(Date), -2))'}>} YearQuarter)

          Expression: Sum({<Date={'$(=QuarterStart(Max(Date), -2))'}>} Sales)

           

          Expression 4.

          Caption - Only({<Date={'$(=QuarterStart(Max(Date), -1))'}>} YearQuarter)

          Expression: Sum({<Date={'$(=QuarterStart(Max(Date), -1))'}>} Sales)

           

          Expression 5.

          Caption - Only({<Date={'$(=QuarterStart(Max(Date)))'}>} YearQuarter)

          Expression: Sum({<Date={'$(=QuarterStart(Max(Date)))'}>} Sales)

           

          Regards,

          Jagan.

          • Re: Need Help in moving column to row
            Jonathan Poole

            I agree this would be best served through a PIVOT table.

             

            For a data model solution you can consider the 'Generic Load'. Which turns dimension values into fields.

             

            Here is a sample load script using your data and screenshot is an output using a simple table object

             

            generic LOAD

                "BP ID",

                "Year" & ' ' & Quarter as YearQuarter,

                Sales

            FROM [lib://community]

            (html, codepage is 1252, embedded labels, table is @1);

             

            Capture.PNG.png

             

            Take note of the funky data model that generic loads create:

             

            Capture.PNG.png