topic Re: Re: Re: Sum by time and date in Script in QlikView

Sum by time and date in Script

Wed, 19 Nov 2014 09:30:12 GMT

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:02 200

01/01/2014 08:00:03 300

02/01/2014 08:00:01 100

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?

Re: Sum by time and date in Script

anbu1984 — Wed, 19 Nov 2014 09:35:29 GMT

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:02,200

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

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

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

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

Re: Sum by time and date in Script

ashfaq_haseeb — Wed, 19 Nov 2014 09:37:48 GMT

HI,

Have a look at attached document.

Simple straight table did that.

Regards

ASHFAQ

Re: Sum by time and date in Script

Wed, 19 Nov 2014 09:50:39 GMT

Hi olle,

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

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

Re: Sum by time and date in Script

Wed, 19 Nov 2014 12:45:26 GMT

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;

Re: Sum by time and date in Script

Wed, 19 Nov 2014 13:07:27 GMT

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

Re: Re: Sum by time and date in Script

Wed, 19 Nov 2014 13:20:48 GMT

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

Re: Sum by time and date in Script

Wed, 19 Nov 2014 13:33:15 GMT

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

Re: Re: Sum by time and date in Script

Thu, 20 Nov 2014 09:10:59 GMT

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

Re: Sum by time and date in Script

Thu, 20 Nov 2014 09:31:07 GMT

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

Re: Sum by time and date in Script

Thu, 20 Nov 2014 10:16:16 GMT

I'm afraid I do not understand

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

Sorry for the hazzle

//O

Re: Re: Sum by time and date in Script

Thu, 20 Nov 2014 10:47:58 GMT

Hi,

see the attached file.see if you want this?

Re: Re: Sum by time and date in Script

Mon, 24 Nov 2014 08:35:41 GMT

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

Re: Sum by time and date in Script

jagan — Mon, 24 Nov 2014 08:40:28 GMT

Hi,

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

LOAD

Date,

Time,

Regards,

Jagan.

Re: Sum by time and date in Script

Mon, 24 Nov 2014 08:51:08 GMT

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

Re: Sum by time and date in Script

puttemans — Mon, 24 Nov 2014 09:31:32 GMT

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.

Re: Re: Sum by time and date in Script

Mon, 24 Nov 2014 11:01:19 GMT

Interesting approach!

I've tried to implement but getting synthetic keys.

Would you mind have a look?

Best REgards,

Olle !

Re: Sum by time and date in Script

puttemans — Mon, 24 Nov 2014 11:10:10 GMT

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;

Re: Re: Sum by time and date in Script

Mon, 24 Nov 2014 11:18:52 GMT

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;

Re: Sum by time and date in Script

puttemans — Mon, 24 Nov 2014 11:30:05 GMT

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)