blank field in excel

[sylvain_piccarreta]

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

[chrismarlow · Accepted solution]

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.

[sylvain_piccarreta]

thanks for your help,

it works. Thanks again