topic Re: Filter lines on empty data in QlikView

Filter lines on empty data

Fri, 23 Sep 2011 12:33:23 GMT

Hi all,

I have a table containing real costs and forecast costs by Project/Service where we would like to filter all the lines where one of the costs (real or forecast) is equal to 0.

In the example, the filter should eliminate the lines 3, 5 and 7.

The problem is not so simple because each line is an agregation of several lines, so that I can't flag the lines in the LOAD.

Exemple:

If my source contains 2 lines

PROJET1 / SERVICE1 / 0 / 50

PROJET1 / SERVICE1 / 20 / 0

I can't eliminate these lines because, in my table, I'll have

PROJET1 / SERVICE1 / 20 / 50

Can anybody help me?

Thxxx

Re: Filter lines on empty data

SunilChauhan — Fri, 23 Sep 2011 12:40:00 GMT

may be like this you want

see the attached file

thanks

Sunil

Re: Filter lines on empty data

its_anandrjs — Fri, 23 Sep 2011 13:46:17 GMT

Hi,

May be do some thing like below

Temp:

LOAD

Distinct

Projet,

Service,

[Cout estimé],

[Cout reel]

FROM

Exemple.xls

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

Data:

Load

if(([Cout estimé]=0 or [Cout reel]=0), Projet) as NewProject

Resident Temp;

Drop table Temp;

See the attached sample file.

Rgds

Anand

Re: Filter lines on empty data

Jason_Michaelides — Fri, 23 Sep 2011 15:46:04 GMT

So I think you only want to filter out those project and service combinations where Sum(Cout estimé)=0 or Sum(Cout reel)=0.

If that's correct, then try changing your script to:

Data:
LOAD Projet, 
     Service, 
     [Cout estimé], 
     [Cout reel],
     Projet & '/' & Service          AS          Key
FROM
[..\..\..\Exemple.xls]
(biff, embedded labels);
ZeroProject:
LOAD
          Key,
          IF(Sum([Cout estimé])=0 OR Sum([Cout reel])=0,1,0)          AS          Flag_ZeroProject
RESIDENT Data
GROUP BY Key;

Then a listbox on Flag_ZeroProject will enable you to filter them. Working example attached.

Hope this helps,

Jason