Script

[mario-sarkis]

Hi please help if its possible,

my case is that i have a table similar to this example

ID               Date             Amount 
1                 2013            5000

1                 2013            4000

2                2013             3000

2                2013              2500

1                2014               200

1                2014              350

2                2014              2000

2               2014               200

how can  i write in the script  to get this OUTPUT

ID                Date             Amount        Count

1                  2013           9000              2

2                  2013           5500              2

1                  2014           550                 2

2                  2014          2200                2

Hope you can help .

[martinpohl · Accepted solution]

add to your script:

load

ID,

Date,

sum(Amount) as Amount,

count(ID) as Count

resident your datatable

group by ID, Date

;

drop table your datatable;

Regards

[jonathandienst · Accepted solution]

I am not sure why you want to do this in script, but here goes:

LOAD ID,

     Date,

     Sum(Amount) As Amount,

     Count(Amount) As Count

FROM .....

GROUP BY ID, Date;

(Replace FROM with RESIDENT if applicable)

[alexandros17]

AAA:
LOAD * Inline [
ID, Date, Amount 
1 , 2013, 5000
1, 2013 , 4000
2, 2013 , 3000
2, 2013 , 2500
1, 2014 , 200
1, 2014 , 350
2, 2014 , 2000
2, 2014 , 200
];

LOAD Sum(Amount) as Amount, ID, Date, Count(ID) as Counts Resident AAA Group By ID, Date;
DROP Table AAA;

[Anonymous]

load * inline [

ID, Date,Amount 

1,2013,5000

1,2013,4000

2,2013,3000

2,2013,2500

1,2014,200

1,2014,350

2,2014,2000

2,2014,200

];



load

ID,

Date,

sum(Amount) as amt,

count(ID) as cnt

Resident temp

Group by ID,Date;