Sum by time and date in Script

[#1]

Hi,

In my script I need to sum transactions executed at the the exact same time and same day.

LOAD
     Date,
     Time,
     SettlementAmount

INPUT Example:

Date                     Time                    SettlementAmount

01/01/2014          08:00:01               100

01/01/2014          08:00:01               100

01/01/2014          08:00:02               200

01/01/2014          08:00:02               200

01/01/2014          08:00:03               300

02/01/2014          08:00:01               100

02/01/2014          08:00:01               100

02/01/2014          08:00:02               200

02/01/2014          08:00:02               200

02/01/2014          08:00:03               300

Expected Output (sum per time and date):

01/01/2014          08:00:01               200

01/01/2014          08:00:02               400

01/01/2014          08:00:03               300

02/01/2014          08:00:01               200

02/01/2014          08:00:02               400

02/01/2014          08:00:03               300

Any ideas?

[anbu1984]

Load Date,Time,Sum(SettlementAmount) As SettlementAmount Group by Date,Time;

Load * Inline [

Date,Time,SettlementAmount

01/01/2014,08:00:01,100

01/01/2014,08:00:01,100

01/01/2014,08:00:02,200

01/01/2014,08:00:02,200

01/01/2014,08:00:03,300

02/01/2014,08:00:01,100

02/01/2014,08:00:01,100

02/01/2014,08:00:02,200

02/01/2014,08:00:02,200

02/01/2014,08:00:03,300 ]

[ashfaq_haseeb]

HI,

Have a look at attached document.

Simple straight table did that.

Regards

ASHFAQ

[#4]

Hi olle,

You can load your input file and after that you can use Groupby clause

like

load

date,

time,

sum(settleamount)

resident t1      //i assume the table you used for input

group by date, time;

then you can delete the table using

drop t1;

it will remove extra data from qvw

With Regards

Harshita Gaur

[#5]

Hi,

I beleive in your solution, however I cannot get it to work when implementing it into my code (pls see below).

Any ideas? 

FOR EACH vSheet IN 'Incoming', 'Outgoing'

TEMP:
LOAD Beneficiary,
     Date,
     OriginalTime,
     SettlementAmount

FROM

(ooxml, embedded labels, table is [$(vSheet)]);

NEXT vSheet

NITIAL:
noconcatenate load
Beneficiary,
Date,
OriginalTime,
SettlementAmount,
IF(Beneficiary= 'THEY',SettlementAmount*-1,SettlementAmount) As Amount
         

Resident TEMP;
Drop Table TEMP;



NoConcatenate
FINAL:
LoaD
Beneficiary,
Date,
OriginalTime,
SettlementAmount,
Amount,
  If(Date <> Previous(Date),Amount,Amount+Peek(RunningBalance)) As RunningBalance

Resident INITIAL
Order By OriginalTime;
Drop Table INITIAL;

[#6]

Hi olle,

i am summing up the values to generate one one value of amount according to date, while in your script you are doing running sum which add the values according to last value. Can you in the end of script doing group by with 'Amount', according to the dimension you want.

Regards

Harshita Gaur

[#7]

Hi Harishita,

I need to add the 'SumAmount' so that I can use it instead of 'Amount'

when generating my 'RunningBalance'. When I've got the 'SumAmount' I can use it as per below:

OLD:

If(Date <> Previous(Date),Amount,Amount+Peek(RunningBalance)) As RunningBalance

NEW:

If(Date <> Previous(Date),SumAmount,SumAmount+Peek(RunningBalance)) As RunningBalance

[#8]

hi

FINAL:
LoaD
Beneficiary,
Date,
OriginalTime,
Sum(Amount) as amount_new

Resident INITIAL

group by Beneficiary,Date, OriginalTime
Order By OriginalTime;
Drop Table INITIAL;

if you want to calculate the value according to benificary, date, and original time you can use it.

in your case it will write the value in each field so the value gets increased.

because like the example you gave here for the first record the value become 100, for next it will become200, 400,...

so when you use this the value become more.

Regards

Harshita Gaur

[#9]

Hi again,

problem is I need the amount_new to be used in the IF statement in "FINAL". So I guess amount_new must be included in the previous load somehow? Pls see my attached sample QV and Excel file. I've tried to implement but I'm apparently missing out on something.

Thanks in advance,

Olle

[#10]

Hi olle,

in the initial2 table, in the group by statment you have to include all the fields, that don't have a aggregation define or if you don't want to calculate the amount according to it then you should remove those field from your table. And you can join the table to let the calculated field to present the table.

With Regards.

Harshita Gaur