topic Re: blank field in excel in QlikView

blank field in excel

sylvain_piccarreta — Thu, 17 Feb 2022 14:37:30 GMT

Hi all

I have an excel file (I can't modify it) with some rows blank (because merged) and I would like to out the first data instead of blank.

Example :

Country	town	value
France	Paris	15000
		1000
Allemagne	Berlin	20000
		2100
		3500
Italie	Rome	20003
		1300
		2300
	Naples	2344
		2311
		3211

I would like to load like a table with each rows completed by the main value

Country	town	value
France	Paris	15000
France	Paris	1000
Allemagne	Berlin	20000
Allemagne	Berlin	2100
Allemagne	Berlin	3500
Italie	Rome	20003
Italie	Rome	1300
Italie	Rome	2300
Italie	Naples	2344
Italie	Naples	2311
Italie	Naples	3211

thanks for your help,

Sylvain

Re: blank field in excel

chrismarlow — Thu, 17 Feb 2022 19:12:42 GMT

Hi,

In QlikView on File Wizard (File Wizard: Transform ‒ QlikView) you can click Next (instead of Finish) & then Enable Transformation Step, then select columns (one by one) & hit Fill to set to fill down.

The code this generates looks something like;

Directory; LOAD Country, town, value FROM [20220217_1.xlsx] (ooxml, embedded labels, table is Sheet1, filters( Replace(1, top, StrCnd(null)), Replace(2, top, StrCnd(null)) ));

Cheers,

Chris.

Re: blank field in excel

sylvain_piccarreta — Fri, 18 Feb 2022 07:25:22 GMT

thanks for your help,

it works. Thanks again