Delete rows if aggr sum is greater than

[andreip21]

Hello,

i have a QVD with:

Order ID || Product ID || Value

1 || 10 || 200

1 || 11 || 70

1 || 12 || 800

2 || 10 || 80

2 || 15 || 400

3 || 18 || 1100

4|| 19 || 500

How do i eliminate directly into the script all the Order ID lines with (AGGR) value greater than 1000?

Total value for Order 1 and 3 is 1070, respectively 1100.

In my example i want to keep only the 3 rows containing Order ID 2 and 4.

Thank you,

Andrei

[siddharth_s3]

Inner join and Having clause should do this. Try below. Pardon if there are syntax error.

TABLE1:

  Load  *  inline [  "Order ID", "Product ID", "Value"

                              1 , 10 , 200

                              1 , 11 , 70

                              1 , 12 ,800

                              2 , 10 ,80

                              2 ,15 ,400

                              3 , 18 ,1100

                              4, 19 ,500                             

      ];

TABLE2:

NOCONCATENATE

LOAD "Order ID", SUM("Value")

RESIDENT TABLE1

GROUP BY "Order ID"

HAVING SUM("Value") < 1000;

TABLE3:

NOCONCATENATE

LOAD * RESIDENT TABLE1

INNER JOIN

LOAD * RESIDENT TABLE2;

DROP TABLE TABLE1,TABLE2;

[andreip21]

Hi Siddharth,

Thank you for your quick answer.

I'm getting the following Syntax error:

Unexpected token: 'HAVING', expected one of: ',', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', ...

TABLE2:

NOCONCATENATE

LOAD "Order ID", SUM("Value")

RESIDENT TABLE1

GROUP BY "Order ID"

>>>>>>HAVING<<<<<< SUM("Value") < 1000

What should i do?

[settu_periasamy · Accepted solution]

May be like this?

T1:
 LOAD * INLINE [
 Order ID ,  Product ID , Value
 1, 10, 200
 1, 11, 70
 1, 12, 800
 2, 10, 80
 2, 15, 400
 3, 18, 1100
 4, 19, 500
 ];
 
 Left Join(T1)
 LOAD [Order ID], if(sum(Value)>1000,'Remove','No') as Flag Resident T1 Group by [Order ID];
 
 NoConcatenate
 T2:
 LOAD [Order ID],[Product ID],Value Resident T1 Where Flag='No';
 
 DROP Table T1; 

[andreip21]

it works perfectly.

thanks a lot!

[shiveshsingh]

T:load * inline

[Order ID , Product ID, Value

1 , 10 , 200

1 , 11 , 70

1 , 12 , 800

2 , 10 , 80

2 , 15 , 400

3 , 18 , 1100

4, 19 , 500];

NoConcatenate

Q:

load sum(Value) as total,[Order ID]

Resident T

group by [Order ID]

;

NoConcatenate

W:

load total,[Order ID]

Resident Q

where total<1000;

drop table Q;

inner Join(T)

Final:load *

Resident W;

drop table W;