3 Replies Latest reply: Feb 16, 2014 4:37 AM by qlik learn RSS

    Converting data from columns to rows

    Steve Zagzebski

      Looking for the best way to take this sample data:

       

      ClientJan-14Feb-14Mar-14Apr-14May-14Jun-14Jul-14Aug-14Sep-14Oct-14Nov-14Dec-14
      Abc Co100125150175200225250275300325350375
      123 Inc200210220230240250260270280290300310

       

      And through scripting add a month year field and amount field and turn into:

       

      ClientMonthYearAmount
      Abc CoJan-14100
      Abc CoFeb-14125
      Abc CoMar-14150
      Abc CoApr-14175
      Abc CoMay-14200
      Abc CoJun-14225
      Abc CoJul-14250
      Abc CoAug-14275
      Abc CoSep-14300
      Abc CoOct-14325
      Abc CoNov-14350
      Abc CoDec-14375
      123 IncJan-14200
      123 IncFeb-14210
      123 IncMar-14220
      123 IncApr-14230
      123 IncMay-14240
      123 IncJun-14250
      123 IncJul-14260
      123 IncAug-14270
      123 IncSep-14280
      123 IncOct-14290
      123 IncNov-14300
      123 IncDec-14310

       

      Thanks for any assistance

       

      Steve

        • Re: Converting data from columns to rows
          Massimo Grossi

          Tab:

          CrossTable  (MonthYear, Amount)

          LOAD * INLINE [

          Client, Jan-14, Feb-14, Mar-14, Apr-14, May-14, Jun-14, Jul-14, Aug-14, Sep-14, Oct-14, Nov-14, Dec-14

          Abc Co, 100, 125, 150, 175, 200, 225, 250, 275, 300, 325, 350, 375

          123 Inc, 200, 210, 220, 230, 240, 250, 260, 270, 280,    290, 300, 310

          ];

           

           

          from online help

          A cross table is a common type of table featuring a matrix of values between two or more orthogonal lists of header data, of which one is used as column headers. A typical example could be to have one column per month. To transform a cross table into a straight table, use a crosstable prefix. The result is that the column headers (e.g. month names) will be stored in one field - the attribute field - and the column data (month numbers) will be stored in a second field: the data field.

          The syntax is:

          crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )

          where:

          attribute field name is the field to contain the attribute values.

          data field name is the field to contain the data values.

          n is the number of qualifier fields preceding the table to be transformed to generic form. Default is 1.

          Examples:

          Crosstable (Month, Sales) Load * from ex1.csv;

          Crosstable (Month,Sales,2) Load * from ex2.csv;

          Crosstable (A,B) Select * from table3;

          • Re: Converting data from columns to rows
            AVIRAL NAG

            Hi Zagzebski

             

            Please see the Attached Qvw.

             

            Regards

            Aviral