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?

[#2]

I'm afraid I do not understand

Can you pls rearrange the script provided, as an example?

Sorry for the hazzle

//O

[#3]

Hi,

see the attached file.see if you want this?

[#4]

Hi Harshita,

We are closing in! Looks really good.

Just one thing. The Data should not be grouped by "Sender", only by Date and OriginalTime.

When I remove Sender from the grouping I receive an error message. Any idea how to work around this?

Kind REgards,

Olle

[jagan]

Hi,

You also need to remove the sender from load statement column list like below

LOAD

Date,

Time,

,

,

,

Regards,

Jagan.

[#6]

Works!

However I need the sender field in my final table, pls see below:

FOR EACH vSheet IN 'Incoming', 'Outgoing'
TEMP:

LOAD
      Sender,
       Date,
     OriginalTime,
     IF(Sender = 'US',SettlementAmount*-1,SettlementAmount) As Amount,
     SettlementAmount
FROM
Sample.xlsx
(ooxml, embedded labels, table is [$(vSheet)]);
NEXT vSheet

t1:
NoConcatenate
LOAD
// Sender,
     Date,
     OriginalTime,
     SUM(Amount) As Amount_New,
       SUM(SettlementAmount) as New_SettlementAmount

Resident TEMP
Group by Date, OriginalTime;
DROP Table TEMP;

Final:
NoConcatenate
LOAD
// Sender,
  Date,
  OriginalTime,
  Amount_New,
  New_SettlementAmount,
  If(Date <> Previous(Date),Amount_New,Amount_New+Peek(RunningBalance)) As RunningBalance

Resident t1
Order By OriginalTime;
DROP TABLE t1



 

[puttemans]

Hi,

How about this way?

FOR EACH vSheet IN 'Incoming', 'Outgoing'
TEMP:

LOAD
      Sender,
     Date&'|'&Originaltime as Link,

      Date,
     OriginalTime,
     IF(Sender = 'US',SettlementAmount*-1,SettlementAmount) As Amount,
     SettlementAmount
FROM
Sample.xlsx
(ooxml, embedded labels, table is [$(vSheet)]);
NEXT vSheet

t1:
NoConcatenate
LOAD
      Link,
     SUM(Amount) As Amount_New,
       SUM(SettlementAmount) as New_SettlementAmount

Resident TEMP
Group by Link;
//DROP Table TEMP;

Map_1:

MAPPING LOAD

Link,

Amount_New

Resident t1;

Map_2,

Link,

New_SettlementAmount

Resident t1;

Final:
NoConcatenate
LOAD
  Sender,
  Date,
  OriginalTime,
  Applymap ('Map_1', Link ) as Amount_New,
  Applymap('Map_2, Link) as New_SettlementAmount,

Resident TEMP

DROP TABLE t1

This way you keep your original data, but you add the sums to it you'd need.

[#8]

Interesting approach!

I've tried to implement but getting synthetic keys.

Would you mind have a look?

Best REgards,

Olle !

[puttemans]

Hi Olle,

You need to lose the temp-table as well, then the sync.key will be gone.

So last line becomes :

DROP TABLE t1, TEMP;

[#10]

Thanks, however a new problem occurs when I need to calculate the running balance in the final table.

Error message says "Cannot find Amont_New". This is a never ending story

Kind Regards,

Olle

FINAL

NoConcatenate
LOAD
  Sender,
  Date,
  OriginalTime,
  Applymap ('Map_1', Link ) as Amount_New,
  Applymap('Map_2', Link) as New_SettlementAmount,

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

Resident TEMP
Order By OriginalTime;
DROP TABLE t1, TEMP; 

[puttemans]

Oh no, for sure it will end somewhere 🙂

The mistake is due to the fact that we create the variable in the same table where you want to use it. That's a bit too much for the program. You could solve with yet another tabel, or with a preceding load.

For the last, you rescript into:

Final:

LOAD *,

if(Date<> Previous(Date), Amount_New,Amount_New+Peek(RunningBalance)) as RunningBalance;

LOAD

   Sender,

Date,
OriginalTime,
Applymap ('Map_1', Link ) as Amount_New,
Applymap('Map_2', Link) as New_SettlementAmount

Resident TEMP
Order By OriginalTime;
DROP TABLE t1, TEMP;

Although I don't know exactly whether you can still use the 'order by' with a preceding load. You could try, safest is to script :

Final2:

Noconcatenate

LOAD *,

if(Date<> Previous(Date), Amount_New,Amount_New+Peek(RunningBalance)) as RunningBalance

Resident Final;

Drop Table Final;

(and remove the If-statement out of the Final table)