Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
I'm afraid I do not understand
Can you pls rearrange the script provided, as an example?
Sorry for the hazzle
//O
Hi,
see the attached file.see if you want this?
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
Hi,
You also need to remove the sender from load statement column list like below
LOAD
Date,
Time,
,
,
,
Regards,
Jagan.
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
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.
Interesting approach!
I've tried to implement but getting synthetic keys.
Would you mind have a look?
Best REgards,
Olle !
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;
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;
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)