topic Excel data into qlikview in QlikView

Excel data into qlikview

Fri, 20 Nov 2020 18:02:30 GMT

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

Re: Excel data into qlikview

sunny_talwar — Fri, 22 Jan 2016 16:33:37 GMT

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;

Re: Excel data into qlikview

Oleg_Troyansky — Fri, 22 Jan 2016 16:39:02 GMT

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!

Re: Excel data into qlikview

Fri, 22 Jan 2016 16:49:39 GMT

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.

Re: Excel data into qlikview

sunny_talwar — Fri, 22 Jan 2016 16:53:40 GMT

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

Re: Excel data into qlikview

Fri, 22 Jan 2016 17:15:56 GMT

hi sunny,

is there any other way to get this?

because i have 100s of lines.

Re: Excel data into qlikview

MK_QSL — Fri, 22 Jan 2016 18:04:26 GMT

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;

Re: Excel data into qlikview

Oleg_Troyansky — Fri, 22 Jan 2016 18:14:41 GMT

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.

Re: Excel data into qlikview

dgreenberg — Fri, 22 Jan 2016 18:38:07 GMT

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.

Re: Excel data into qlikview

jagan — Mon, 25 Jan 2016 00:41:07 GMT

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.