topic Excel file with merged cells to be imported in QlikView

Excel file with merged cells to be imported

Tue, 08 Jan 2013 15:07:16 GMT

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_NAME	DEPT_NAME	Bud Gross Sales
APPARELS	FOOTWEAR	142
	HOME LINEN	143
	INTIMATE WEAR	55
	KIDS WEAR	40
	LADIES WEAR	56
	MEN'S WEAR	126
	PERSONAL ACCESSORIES	45
	Total	608
APPLIANCES	AUDIO & SOUND	32
	BIG HOUSEHOLD	194
	COMPUTER	57
	DIGITAL & COMMUNICATION	57
	SMALL HOUSEHOLD	110
	TV - VIDEO	194
	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 — Tue, 08 Jan 2013 15:26:18 GMT

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

Re: Excel file with merged cells to be imported

Tue, 08 Jan 2013 15:31:51 GMT

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

Thank you very much!!!!