Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

How to import a row of data from Excel into Qlikview

Hello,

I am trying to import a row of data from Excel into Qlikview. Unfortunately, I am unable to change the row (consisting of location ID #'s) to columns due to contractual reasons. I want to have this row of data be treated like a column, in that I want to be able to load it in as shown below so I can use it for reference with SQL Server.

Row 10 has the ID's I need to be able to use to link the entries to the SQL data.

The 'RowNo() = 10' shown below is not working on reading in Row 10. I would really appreciate any help in figuring out how to correctly write this statement to read in Row 10.

LOAD D,
E,
F,
G,
H,
I,
J,
K,
RowNo() = 10

FROM
[Location]
(
ooxml, no labels, table is

)
WHERE RecNo() >= 11;

Any help is greatly appreciated.

Thanks,

Alec

1 Solution

Accepted Solutions

Re: How to import a row of data from Excel into Qlikview

Try something like this:

load * FROM [Location]  (ooxml, no labels, table is

)
WHERE RecNo() = 10; // if you load with labels you would need the #11

- Marcus

4 Replies
trdandamudi
Honored Contributor

Re: How to import a row of data from Excel into Qlikview

Can you be little more specific and attach sample excel file to try out please,,, Also not clear about the where condition in the code.

Re: How to import a row of data from Excel into Qlikview

Try something like this:

load * FROM [Location]  (ooxml, no labels, table is

)
WHERE RecNo() = 10; // if you load with labels you would need the #11

- Marcus

Not applicable

Re: How to import a row of data from Excel into Qlikview

Thank you, this is exactly what I was looking for. One question though, if I only want to load columns H-AG, how can I do that? I've tried

LOAD H-AG

and

LOAD -[AG]

and it is not working.

LOAD H:AG also does not work.

Do you know how I can have it load only those columns and not all of them (*)?

Thank you,

Alec

Re: How to import a row of data from Excel into Qlikview

If you don't want to load all columns (which you could simplify with the wildcard) you will need to specify each single column - if you used the wizard for the load-statement this won't need much efforts.

To shorten a load-statement you could variables - whereby you will add some complexity and reduce to some degree the readability. But nevertheless you could do:

set vColumns = H, I, J, ....;

and then:

load $(vColumns) From YourSource;

Another possibility could be to load all columns with the wildcard and then drop all unwanted columns but this will be only useful if there are much more columns to load then not to load and you will in each case increase the load-times.

- Marcus

Community Browser