Filter lines on empty data

[#1]

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

[SunilChauhan]

may be like this you want

see the attached file

thanks

Sunil

[its_anandrjs]

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

[Jason_Michaelides]

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