11 Replies Latest reply: Aug 10, 2015 10:10 AM by Sunny Talwar RSS

    Transpose data

      Hi All,

       

      I have teh date as given below in excel .

       

       

      Bankid4/1/20154/2/20154/3/20154/4/20154/5/20154/6/2015
      117155685135003733
      1193366547467000309
      119482040844601320388
      11952274956410000
      120250046545211000172
      120678048434587000

       




      But the data needs to be captures in 3 columns ( Bank ID , Date, Marcount)

       

      for ex

       

       

      Bank IDDateMar count
      11715564/1/201585
      11715564/2/2015135
      11715564/3/20150
      11715564/4/20150
      11715564/5/201537
      11715564/6/201533
        • Re: Transpose data
          Sunny Talwar

          You need to use CrossTable Load here: The Crosstable Load

            • Re: Transpose data
              Sunny Talwar

              Try the following Script

               

              Table:

              LOAD * Inline [

              Bankid, 4/1/2015, 4/2/2015, 4/3/2015, 4/4/2015, 4/5/2015, 4/6/2015

              1171556, 85, 135, 0, 0, 37, 33

              1193366, 547, 467, 0, 0, 0, 309

              1194820, 408, 446, 0, 132, 0, 388

              1195227, 495, 641, 0, 0, 0, 0

              1202500, 465, 452, 110, 0, 0, 172

              1206780, 484, 345, 87, 0, 0, 0

              ];

               

              CrossTable:

              CrossTable (Date, Value)

              LOAD *

              Resident Table;

               

              FinalTable:

              NoConcatenate

              LOAD Bankid,

                Date#(Date, 'MM/DD/YYYY') as Date,

                Value

              Resident CrossTable;

               

              DROP Tables Table, CrossTable;

               

              Output in Table Box Object:

               

              Capture.PNG

                • Re: Transpose data

                  Hi,

                   

                  The only issue is when i upload from excel the date field is not coming correctly.

                  It still shows as 42095,42096

                    • Re: Transpose data
                      Sunny Talwar

                      Convert the number to Date using the Date function in a resident load

                        • Re: Transpose data

                          Hi,

                           

                          Following is the code.

                           

                          tmp:

                          CrossTable (Date1, Marcount)

                          LOAD Bankid,

                               [42095],

                               [42096],

                               [42097],

                               [42098],

                               [42099],

                               [42100],

                               [42101],

                               [42102],

                               [42103],

                               [42104],

                               [42105],

                               [42106],

                               [42107],

                               [42108],

                               [42109],

                               [42110],

                               [42111],

                               [42112],

                               [42113],

                               [42114],

                               [42115],

                               [42116],

                               [42117],

                               [42118],

                               [42119],

                               [42120],

                               [42121],

                               [42122],

                               [42123],

                               [42124],

                               [42125],

                               [42126],

                               [42127],

                               [42128],

                               [42129],

                               [42130],

                               [42131],

                               [42132],

                               [42133],

                               [42134],

                               [42135],

                               [42136],

                               [42137],

                               [42138],

                               [42139],

                               [42140],

                               [42141],

                               [42142],

                               [42143],

                               [42144],

                               [42145],

                               [42146],

                               [42147],

                               [42148],

                               [42149],

                               [42150],

                               [42151],

                               [42152],

                               [42153],

                               [42154],

                               [42155],

                               [42156],

                               [42157],

                               [42158],

                               [42159],

                               [42160],

                               [42161],

                               [42162],

                               [42163],

                               [42164],

                               [42165],

                               [42166],

                               [42167],

                               [42168],

                               [42169],

                               [42170],

                               [42171],

                               [42172],

                               [42173],

                               [42174],

                               [42175],

                               [42176],

                               [42177],

                               [42178],

                               [42179],

                               [42180],

                               [42181],

                               [42182],

                               [42183],

                               [42184],

                               [42185],

                               [42186],

                               [42187],

                               [42188],

                               [42189],

                               [42190],

                               [42191],

                               [42192],

                               [42193],

                               [42194],

                               [42195],

                               [42196],

                               [42197],

                               [42198],

                               [42199],

                               [42200],

                               [42201],

                               [42202],

                               [42203],

                               [42204],

                               [42205],

                               [42206],

                               [42207],

                               [42208],

                               [42209],

                               [42210],

                               [42211],

                               [42212],

                               [42213],

                               [42214],

                               [42215],

                               [42216]

                           

                           

                          FROM

                          [C:\Users\1414571\Desktop\Qlik Productivity.xlsx]

                          (ooxml, embedded labels, table is Sheet1);

                           

                           

                          Final:

                          Load Bankid,

                           

                           

                          Date#(Date1, 'MM/DD/YYYY') as Processdate,

                          Marcount

                          Resident tmp;

                          Drop Table tmp;

                           

                          Inspite oif it it still shows in number format

                  • Re: Transpose data
                    Anil Kumar

                    Hi,

                     

                    Please follow this steps to achieve your desired output:

                     

                    Step-1:Go to Edit Script and select the data source (Excel, CSV ...)

                    Step-2: Click on next button twice and after that click on CrossTable.

                    Above, you can select Qualifier fields, Name for Attribute Filed and Data Values. You can see the color combination with these related filed also. Press OK. Now Data is converted to Straight Table.

                    Now, Click on Finish.


                     

                    • Re: Transpose data

                      Hi Rajesh

                       

                      PFA

                       

                      Regards

                      Chitra