Excluding Zeros in a table

[#1]

Hi Experts,

I have a table which contains three fields, One is Product, second is Amount , and another is quantity.

I need to exclude the products from table which doesn't have sales (that means Zero in amount field).

I am new to qlikview can any one help me in this.

Thanks in advance.

Pradeep

[#2]

Hi pradeep,

Please find the below example and try this Load statement,

Product	Amount	Quantity
A	1	2
B	0
C	Null
D	10	3
E	5	4
F	Null

LOAD Product,

     Amount,

     Quantity

FROM

<Your File Name>

(ooxml, embedded labels, table is Sheet1)

WHERE(Amount >'0');

Reagrds,

Senthil

[er_mohit]

load

if(Amount>0,Product) as Product,

if(Amount>0,Amount) as Amount

Quantity

from your datasource;

or try this way

load

Product,

Amount

Quantity

from your datasource

where Amount>0;

hope it helps

[#4]

Hi Senthil and Mohit,

Thanks for your answers!

But the thing is that i have two sheets . In first sheet i need all the products(Even if there is no sales) for another analyze.

In the second sheet i need the products only with the sales.

Could you please suggest the way using set Analysis.

[#5]

Hi Mohit,

Thanks for your answers!

But the thing is that i have two sheets . In first sheet i need all the products(Even if there is no sales) for another analyze.

In the second sheet i need the products only with the sales.

Could you please suggest the way using set Analysis.

[#6]

Hi Pradeep,

Try this Load statement,

for i = 1 To < No of sheet> // For Ex, 4

LOAD Region,

     Country,

     Year,

     Month,

     Rep,

     Sales

FROM

< Your Excel file Name >

(ooxml, embedded labels, table is [Page$(i)]);

NEXT

Here, I am mentioned sheet name is Page1, Page2...

Regards,

Senthil...