topic Re: loading data from XL in QlikView

loading data from XL

prabhas277 — Wed, 25 Nov 2020 16:16:04 GMT

If I Want to Skip specific rows in Excel during Load how do we need to do that?

Re: loading data from XL

sunny_talwar — Wed, 27 Apr 2016 10:36:27 GMT

Maybe something like this:

LOAD *

FROM Excel

Where not Match(RowNo(), 15, 20, 25);

the above code should exclude row number 15, 20 and 25

Re: loading data from XL

awhitfield — Wed, 27 Apr 2016 10:43:48 GMT

Hi Prabhas,

you can use the Table Files> File Wizard: Transform, this will create the resultant script for you, e.g

HTH

Andy

Re: loading data from XL

Anonymous — Wed, 27 Apr 2016 10:50:36 GMT

load the excel file and the than write the code where not exits rowno()= give the numbers

Re: loading data from XL

Anonymous — Wed, 27 Apr 2016 10:51:07 GMT

load the excel file and the than write the code where not exits (rowno(),give the numbers)

Re: loading data from XL

prabhas277 — Wed, 27 Apr 2016 12:43:51 GMT

hi sunny,

my data like this

A B C D

1 2 3 4

5 6 7 8

9 1 2 3

4 5 6 7

i need to skip 3 row only, remaining should be same please write the code for that @ !

Re: loading data from XL

sunny_talwar — Wed, 27 Apr 2016 12:47:53 GMT

Like this:

Table:

LOAD * Inline [

A, B, C, D

1, 2, 3, 4

5, 6, 7, 8

9, 1, 2, 3

4, 5, 6, 7

]

Where RowNo() <> 3;

I am using inline load, but you can do the same for your excel load

Table:

LOAD A,

FROM Excel

Where RowNo <> 3;

Re: loading data from XL

jyothish8807 — Wed, 27 Apr 2016 12:53:02 GMT

Hi Prabhas,

There are multiple way to do that.

1. Do you have a primary key in this table ? If yes then you can limit it using a where clause.

2. Else add rowno() in the table and limit according the row number.

eg, Load *

from table.xls

where A<>9;

//amusing A is primary key

2nd option.

Load *,

roeno() as Sino

from table.xls

where not Match(RowNo(), 3);

Regards