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

[sunny_talwar · Accepted solution]

But why an extra load when it can be done in two?

Table:

LOAD *,

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

LOAD * INLINE [

    ID, Value

    1, 10

    2, -20

    3, 5

    4, -10

    5, -100

    6, 20

    7, 35

    8, 40

    9, 30

];

Left Join (Table)

LOAD NeededStuff,

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

Resident Table

Where NeededStuff = 'Needed'

Group By NeededStuff;

[timpoismans]

'cause it still showed me the Flag field filled in on every line for some reason. Refreshed again and works perfect.

razvan.brais‌ Summy's solution works like a charm.

[razvan_brais]

Thank you Sunny , and Tim. Both of your answers were helpfull.

Thanks again

[niccolo_chiodar]

If you can target rows you want to sum, in this case with an S in the target column, than the following approach should work.

data:

LOAD * INLINE [

id | value | target

1 | 10 | S

2 | -20 | S

3 | 5 | S

4 | -10 | S

5 | -100 | N

6 | 20 | N

7 | 35 | N

8 | 40 | N

9 | 30 |N

](delimiter is '|');

LEFT JOIN (data)

LOAD

'S' as target

, If( Sum(value)>0 ,'A','P') as flag

Resident data

Where target = 'S'

;

[razvan_brais]

Hello,

I came back with one more question. Let`s say that I have one more column in which I have date for each value. I would like to calculate the total for each year and apply the same comparison as said before.

For example :

TrzDate	ID	Value	Flag
2018-01-28	1	10	P
2018-01-23	2	-20	P
2018-01-17	3	5	P
2018-02-21	4	-10	P
2017-04-28	1	100	A
2017-02-28	2	20	A
2017-07-31	3	30	A
2017-11-17	4	40	A
2017-12-02	5	30
2017-09-03	6	20
2017-08-12	7	30
2017-06-14	8	40
2017-01-16	9	50
2017-02-22	10	60

So , I select the 2018-01-17 and the needed ID`s for calculation are 1,2,3,4 , I will need to calculate the total value of these 4 ID`s , for the year 2018 : -15 < 0 then the Flag is P.

But , if I select 2017-04-28 the calculation of same ID`s will be : 190 which is grater than 0 so , the Flag will be A.

How can I achieve this following the script that you suggested?

Thank you

[razvan_brais]

I have tried something like this :

set vListOfExceptions1 = '1','2','3','4'

Table:

load

ID,

sum(Value) as Value,

if(match(ACCT,$(vListOfExceptions1)),'Needed') as NeededStuff1,

DATE,

Timestamp(DATE,'YYYY') as Year,

Resident Table1

Group by

ID,DATE;

Left Join (Table)

LOAD

Year,

  NeededStuff1,

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

Resident Table

Where NeededStuff1 = 'Needed'

Group By NeededStuff1,Year;

Think it would work?

[sunny_talwar]

Create Year like this

SET vListOfExceptions1 = '1','2','3','4'

Table:

LOAD ID,

     Sum(Value) as Value,

     If(Match(ACCT,$(vListOfExceptions1)),'Needed') as NeededStuff1,

     DATE,

    Year(DATE) as Year,

Resident Table1

Group by ID, DATE;

Left Join (Table)

LOAD Year,

     NeededStuff1,

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

Resident Table

Where NeededStuff1 = 'Needed'

Group By NeededStuff1,Year;

[sunny_talwar]

With your sample

Table:

LOAD *,

Year(TrzDate) as Year,

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

LOAD * INLINE [

    TrzDate, ID, Value

    2018-01-28, 1, 10

    2018-01-23, 2, -20

    2018-01-17, 3, 5

    2018-02-21, 4, -10

    2017-04-28, 1, 100

    2017-02-28, 2, 20

    2017-07-31, 3, 30

    2017-11-17, 4, 40

    2017-12-02, 5, 30

    2017-09-03, 6, 20

    2017-08-12, 7, 30

    2017-06-14, 8, 40

    2017-01-16, 9, 50

    2017-02-22, 10, 60

];

Left Join (Table)

LOAD NeededStuff,

Year,

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

Resident Table

Where NeededStuff = 'Needed'

Group By NeededStuff, Year;

[razvan_brais]

Magic, Thank a lot.