2 Replies Latest reply: Jan 8, 2013 10:31 AM by michele casoli RSS

    Excel file with merged cells to be imported

      Hello Folks,

       

      I have exported a pivot table from qlikview to excel, and now to read those informations I have problem because qlikview reads the merged cells only for the first line of its group...  anyone can help me? 

       

      DIV_NAMEDEPT_NAMEBud Gross Sales
      APPARELSFOOTWEAR142
      HOME LINEN143
      INTIMATE WEAR55
      KIDS WEAR40
      LADIES WEAR56
      MEN'S WEAR126
      PERSONAL ACCESSORIES45
      Total 608
      APPLIANCESAUDIO & SOUND32
      BIG HOUSEHOLD194
      COMPUTER57
      DIGITAL & COMMUNICATION57
      SMALL HOUSEHOLD110
      TV - VIDEO194
      Total 645

       

       

      I just pasted a small piece of a table I found on the forum to let you understand what my problem is, right now qlikview only imports these two lines:

      APPLIANCES     AUDIO & SOUND      32

      APPARELS        FOOTWEAR               142

       

       

      thanks you in advance!!

       

      best Regards

      Michele

        • Re: Excel file with merged cells to be imported
          Gysbert Wassenaar

          If you use the table file wizard you can in the second step click on Enable Transformation Step. Click on the Fill tab, click on column 1 and click on the Fill button. Next click on Cell condition and accept the default (Cell Value is empty). Click Ok and choose as Fill Type above. Click the OK button to return to the Transform wizard screen. Click on

          Next and then click Finish. That's enough to fill in the empty fields. That should create a script like:

           

          LOAD DIV_NAME,

               DEPT_NAME,

               [Bud Gross Sales]

          FROM

          mergedcells.xls

          (biff, embedded labels, table is [Sheet1$], filters(

          Replace(1, top, StrCnd(null))

          ));

           

          There's an alternative that uses the peek function to achieve the same:

           

          LOAD if(len(trim(DIV_NAME))=0,peek('DIV_NAME'),DIV_NAME) as DIV_NAME,

               DEPT_NAME,

               [Bud Gross Sales]

          FROM

          mergedcells.xls

          (biff, embedded labels, table is [Sheet1$]);