Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Load excel excluding some value

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.

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

6 Replies
jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.

Anonymous
Not applicable
Author

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

Not applicable
Author

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

Anonymous
Not applicable
Author

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.