Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Excel file with merged cells to be imported

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$]);


talk is cheap, supply exceeds demand
2 Replies
MVP & Luminary
MVP & Luminary

Re: Excel file with merged cells to be imported

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$]);


talk is cheap, supply exceeds demand
Not applicable

Re: Excel file with merged cells to be imported

Wow it was simple .... now that I know it!!!

Thank you very much!!!!