Excel data into qlikview

[#1]

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

[sunny_talwar · Accepted solution]

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;

[Oleg_Troyansky]

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!

[#4]

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.

[sunny_talwar]

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);

[#6]

hi sunny,

is there any other way to get this?

because i have 100s of lines.

[MK_QSL]

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;

[Oleg_Troyansky]

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.

[dgreenberg]

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.

[jagan]

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.