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:


      Abc Co100125150175200225250275300325350375
      123 Inc200210220230240250260270280290300310


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


      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



        • Re: Converting data from columns to rows
          Massimo Grossi


          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 )


          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.


          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.