Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
double7671
Creator
Creator

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
MVP & Luminary
MVP & Luminary

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
MVP & Luminary
MVP & Luminary

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

Not applicable

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
MVP & Luminary
MVP & Luminary

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.

amarnathd
Contributor III
Contributor III

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

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

double7671
Creator
Creator

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.