Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
may be like this you want
see the attached file
thanks
Sunil
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
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