Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Excel data into qlikview

Hi Experts,

i have an excel sheet with 300 lines.

but i need to load specific lines alone into qlikview.

ex: i have to load from line 10 till line 20.

how can i do that.

Pls help

1 Solution

Accepted Solutions

by line you mean rows?

May be this:

Table:

First 20

LOAD *

From Source

Where RecNo() >= 10;


Table:

LOAD A

FROM

Community_201132.xlsx

(ooxml, no labels, table is Sheet1)

Where RecNo() >= 10 and RecNo() <= 20;

View solution in original post

9 Replies

by line you mean rows?

May be this:

Table:

First 20

LOAD *

From Source

Where RecNo() >= 10;


Table:

LOAD A

FROM

Community_201132.xlsx

(ooxml, no labels, table is Sheet1)

Where RecNo() >= 10 and RecNo() <= 20;

View solution in original post

MVP & Luminary
MVP & Luminary

Or, you can use Transformation tools in the File Wizard and declare all undesired lines as garbage.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

Not applicable

thanks sunny.

one more doubt.

i want to delete line no. 15 & 17 between 10 and 20

is it possible?

if so, please explain that too.

May be this:

Table:

LOAD A

FROM

Community_201132.xlsx

(ooxml, no labels, table is Sheet1)

Where Match(RecNo(), 10,11, 12, 13, 14, 16, 18, 19, 20);

Not applicable

hi sunny,

is there any other way to get this?

because i have 100s of lines.

MVP
MVP

You have to store or use Inline load for the range of lines you need to load.

Temp:

Load * Inline

[

  From, To

  11, 15

  16, 16

  18, 20

];

Temp2:

Load From + IterNo() - 1 as NO Resident Temp

While From + IterNo() - 1 <= To;

Drop Table Temp;

Table:

LOAD

  Field

FROM

Community_201132.xlsx

(ooxml, embedded labels, table is Sheet1)

Where Exists(NO, RecNo());

Drop Table Temp2;

MVP & Luminary
MVP & Luminary

The other way is to use the Transformation wizard, as I suggested earlier, and to define the logical condition for the "garbage" lines - I'm sure there is a logical reason for excluding lines 15-17, besides their numbers... These logical conditions can cover the hundreds of lines that you mentioned.

Luminary
Luminary

Sometimes it's also easier to just do something in excel.

It's easy enough to write a formula in a new column that flags records to be loaded and then just use a where clause on your QlikView load where flag=1.

It's just a thought and if the spreadsheet is constantly being updated not a good thought but if it's a one shot deal sometimes it's just easier to change the source rather than fight the tool.

MVP & Luminary
MVP & Luminary

Hi,

Table:

LOAD A

FROM

Community_201132.xlsx

(ooxml, no labels, table is Sheet1)

Where RecNo() >= 10 and RecNo() <= 20 AND NOT Match(RecNo(), 15, 17);


Hope this helps you.


Regards,

Jagan.