Total sum of multiple ID`s

[razvan_brais]

Hy guys. Hope you can help me with one problem.

I want to mark some id`s with 'A' or 'P' depending on the sum of all ID`s.

For example i have this values

ID	Value
1	10
2	-20
3	5
4	-10

I would like to calculate the total sum . S =10+ (-20) + 5 + (-10) = -15 and if  S >=0 then Flag = 'A ' else Flag = 'P';

The final table should look something like this :

ID	Value	Flag
1	10	P
2	-20	P
3	5	P
4	-10	P

The flag of each ID is P because the sum of all values is lower than 0.

I tried with this script :

load

ID,

if(Value>0,'A','P') as Flag

;

load

ACCT ,

sum(Value) as Value

Resident Table1

Where Wildmatch(ID,'1','2','3','4');

But it`s not ok.

Can you please help me with some suggestions?

Thank you

[Anil_Babu_Samineni]

May be this?

Sample:

LOAD * Inline [

ID, Value

1, 10

2, -20

3, 5

4, -10

];

Left Join

LOAD If(Sum(Value)>0, 'A','P') as Flag

Resident Sample;

[sunny_talwar]

Try this

Table:

LOAD ID,

     VALUE

From ....;

Left Join (Table)

LOAD If(Sum(VALUE) < 0, 'A', 'P') as Flag

Resident Table;

[timpoismans]

Hello

A possible solution:

     If(Sum(total Value)>0,'A','P')

This will calculate the total sum of all Values and evaluate the If condition based on the result.
This is a measure in a table though, and not a part of the script.

[razvan_brais]

This is what i`m looking for , but i need to calculate in script , not as an expression in interface.

[sunny_talwar]

Have you tried this already?

[timpoismans]

Sunny's script should work from what I can tell then as a solution.

[razvan_brais]

Sorry that I haven`t been completely clear.

I want to calculate the sum only for specific ID`s and mark thos specific ID`s with A or P.

ID	Value	Flag
1	10	P
2	-20	P
3	5	P
4	-10	P
5	-100
6	20
7	35
8	40
9	30

So the sum should be calculated only for ID`s from 1 to 4 , and mark only those 4 ID`s , the rest should remain blank.

With Sunny`s method all ID`s from the table will get the Flag 'P' .

[sunny_talwar]

Try this

Table:

LOAD ID,

    If(Match(ID, 1, 2, 3, 4), 'Needed') as NeededStuff,

    VALUE

From ....;

Left Join (Table)

LOAD NeededStuff,

    If(Sum(VALUE) < 0, 'A', 'P') as Flag

Resident Table

Where NeededStuff = 'Needed'

Group By NeededStuff;

[timpoismans]

Based on Sunny's script:

Table:

LOAD

     ID,

     If(Match(ID, 1, 2, 3, 4), 'Needed') as NeededStuff,

     Value

From ....;

Left Join (Test)

LOAD

If(Sum(Value) > 0, 'A', 'P') as Flag

Resident Test

Where NeededStuff = 'Needed';

Test2:

Load

     ID,

     Value,

     If(NeededStuff='Needed',Flag,Null()) as Flag

Resident Test;

Drop Table Test;