Load excel excluding some value

[Anonymous]

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.

[jagan · Accepted solution]

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.

[#3]

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]

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]

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

[#6]

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]

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.