topic Re: Load excel excluding some value in QlikView

Load excel excluding some value

Anonymous — Mon, 16 Mar 2015 01:20:19 GMT

I have two excel tables as attached. I want to load all items sales records but excluding supplier B's items,

how could I write the load script? Can you please share the script?

Pls don't post a qvw, I could not open it as my edition is personal. Thanks.

Re: Load excel excluding some value

jagan — Mon, 16 Mar 2015 02:11:32 GMT

Hi,

Try like this

Items:

LOAD Item,

supplier

FROM

ItemInfo.xls

(biff, embedded labels, table is Sheet1$)

WHERE supplier <> 'B';

Sales:

LOAD Item,

Sales

FROM

ItemSales.xls

(biff, embedded labels, table is Sheet1$)

WHERE Exists(Item);

Regards,

Jagan.

Re: Load excel excluding some value

Mon, 16 Mar 2015 03:43:12 GMT

Try this code:

SupplierData:

LOAD Item,

supplier

FROM

(biff, embedded labels, table is Sheet1$)

where supplier <> 'B';

SalesData:

join(SupplierData)

LOAD Item,

Sales

FROM

(biff, embedded labels, table is Sheet1$)

where Exists (Item);

Regards

Lathaa

Re: Load excel excluding some value

jagan — Mon, 16 Mar 2015 03:55:18 GMT

Hi,

You can also try Left Keep like below

Items:

LOAD Item,

supplier

FROM

ItemInfo.xls

(biff, embedded labels, table is Sheet1$)

WHERE supplier <> 'B';

Sales:

Left Keep(Items)

LOAD Item,

Sales

FROM

ItemSales.xls

(biff, embedded labels, table is Sheet1$);

Regards,

Jagan.

Re: Load excel excluding some value

Anonymous — Mon, 16 Mar 2015 03:57:34 GMT

LOAD Item,

supplier

FROM

(biff, embedded labels, table is Sheet1$)

WHERE(supplier <> 'B');

LOAD Item,

Sales

FROM

(biff, embedded labels, table is Sheet1$)

WHERE Exists(Item);

Re: Load excel excluding some value

Mon, 16 Mar 2015 10:05:14 GMT

Hi,

Try this expression. May It should be correct.

ItemInfo:

LOAD

Item,

supplier

FROM

Community\ItemInfo.xls

(biff, embedded labels, table is Sheet1$)

WHERE supplier <> 'B';

Sale:

LOAD Item,

Sales

FROM

Community\ItemSales.xls

(biff, embedded labels, table is Sheet1$)

Where Exists (Item,Item);

Re: Load excel excluding some value

Anonymous — Mon, 16 Mar 2015 21:25:28 GMT

Thanks. Both options work. I saw couple posts mentioned Left Keep () is faster than Where exists (), but in my case, with 20M lines data from qvd, the latter option is faster, loading time wise, not big different to me. But after data is loaded, calculation wise, it is much faster than Left Keep. My machine is win8.1 pad with 2G memory, not sure why.