1 Reply Latest reply: Feb 23, 2016 9:48 AM by Zeeshanuddin Syed RSS

    Cross table with multiple value fields

    Zeeshanuddin Syed

      Hi Gurus,

       

      I am trying to transform an xls into a more reportable format in Qlikview, xls format is unfortunately is as below

       

      DeptNo
      DeptName
      Date1
      Exp1
      Date2
      Exp2Date3Exp3Date4Exp4Date5Exp5
      1accounts20/02/20161019/02/20161018/02/20161017/02/20161016/02/201610
      2marketing20/02/20162019/02/20162018/02/20162017/02/20162016/02/201620
      3sales20/02/20163019/02/20163018/02/20163017/02/20163016/02/201630

       

      I want to transform this data into below format , I tried cross table but was not able to work out how to do it easily. Please help !

       

      DeptnoDeptNameDateExpense
      1accounts20/02/201610
      1accounts19/02/201610
      1accounts18/02/201610
      1accounts17/02/201610
      1accounts16/02/201610
      2marketing20/02/201620
      2marketing19/02/201620
      2marketing18/02/201620
      2marketing17/02/201620
      2marketing16/02/201620
      3sales20/02/201630
      3sales19/02/201630
      3sales18/02/201630
      3sales17/02/201630
      3sales16/02/201630
        • Re: Cross table with multiple value fields
          Zeeshanuddin Syed

          Hi Guys,

          I have achieved this by a short script like as below

           

          [Raw_Data]:

          LOAD DeptNo,  DeptName, Date1, Exp1, Date2, Exp2, Date3, Exp3, Date4, Exp4, Date5, Exp5

          FROM xls;

           

          set a=2;

           

          NoConcatenate

          [Final]:

          Load DeptNo, DeptName, Dat1 as Date, Exp1 as Expense resident [Raw_data];

           

          Do while a<6

           

          Concatenate (Final)

          Load DeptNo, DeptName, Date$(a) as Date, Exp$(a) AS Expense

          resident [Raw_Data];

           

          Let a=a+1;

           

          Loop

           

          drop table [Raw_Data];