Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
sylvain_piccarreta

blank field in excel

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

1 Solution

Accepted Solutions
chrismarlow
Specialist II
Specialist II

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.

20220217_1.png

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.

 

View solution in original post

2 Replies
chrismarlow
Specialist II
Specialist II

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.

20220217_1.png

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
Author

thanks for your help,

 

it works. Thanks again