topic Re: Loading a balance table missing daily records in QlikView

Loading a balance table missing daily records

Sun, 15 May 2011 11:04:03 GMT

Hello

I am trying to upload a transactional table with the following simple structure (Date , Balance) ....

In case there is no balance movement during a day, then no record do exist, for exmaple

On 1/4/2011 the balance is 10,000 US$ , therefore will be a record on the table as (1/4/2011 - 10,000$)

On 2/4/2011, a transaction was done by 300. then there will be a record on the table as (2/4/2011 - 9700$)

On 28/4/2011, a transactionw as done of 2000$, then a record will be on the table as (28/4/2011 - 7700$)

so by end of this example, the table will have only 3 records,

My question

How can I build an application that show the balance of each day even for the days not found in the table

also, which commands to use if I like to see the differences bewteen each day and its previous day with reporting as a movement chart

regards,

Re: Loading a balance table missing daily records

erichshiino — Sun, 15 May 2011 14:35:10 GMT

Hi,

One approach is to create everything on the script. This will reduce the complexity on the interface.

I create a small example for this.

Basically I run throught the table to get min and max dates, then I created all the dates between min and max.

After this I read the table again filling the gaps with the previous known value. At the same time, I created another field with the differences between the current value and the last known one.

Check app attached.

Hope it helps,

Erich

tBalance:
LOAD * INLINE [
    Date, Balance
    01/04/2011, 1000
    02/04/2011, 9700
    28/04/2011, 2000
];
load min(Date) as minDate resident tBalance;
varMinDate = peek('minDate');
load max(Date) as maxDate resident tBalance;
varMaxDate = peek('maxDate');
refDate:
load date($(varMinDate)+rowno()-1) as Date
Autogenerate(num($(varMaxDate)-$(varMinDate))+1) ; 
left join(refDate)
Load Date, Balance resident tBalance
;
; 
drop table tBalance; 
//NoConcatenate
load Date, if(IsNull(Balance),peek('NewBalance'), Balance) as NewBalance,
 if(IsNull(Balance), peek('NewBalance'), Balance)-peek('NewBalance') as var
resident refDate
order by Date;
drop table refDate;

Loading a balance table missing daily records

Mon, 16 May 2011 13:06:58 GMT

Many thanks Erich for the quick response

But the number of accounts I have is 1 million account, and maintaining 5 years data which will lead to have a table of 1 million * 5 * 365 ....

I tried to use your script within my code, but as I am fresh in QlickView, I was not able to manage it ... any help

my existing code is as the following to load the balance table

load Id,

account_no,

code,

currency,

balance_date,

balance;

SQL SELECT *

FROM bi.dbo."acounts_balances;

Loading a balance table missing daily records

gandalfgray — Mon, 16 May 2011 13:55:59 GMT

If your goal is just to show a chart like this:

you don't need to generate records for every day,

instead you just use a line chart with the following special settings:

set the "Display Option" to "Plateau, leading"

and set the dimension axis to "Continuos":

My load script for this example just looks like this:

Transactions:

LOAD * INLINE [

Date, Transaction

1/4/2011, 10000

2/4/2011, -300

10/4/2011, 900

28/4/2011, -2000

];

Re: Loading a balance table missing daily records

gandalfgray — Tue, 17 May 2011 06:41:38 GMT

One more important thing I forgot to mention,

you need to set "Full accumulation" for the sum(transaction) expression:

Loading a balance table missing daily records

Tue, 17 May 2011 13:11:00 GMT

Thanks Erish for the help...

The objective is to calculate the account holder behavior based on the difference and how long he keeps his holdings

with your script I tried to apply it but it didn't work as I am confused of how using the SQL load and the resident fields, so I am attaching here my script hope this will clear for me how to load the table again with the leftjoin statement /////

tBalance:
Load
account_id     as    account_Key ,
currency      as    Position_Currency ,
position_date           as    Position_Date ,
Bal        as    Position_Bal;
SQL SELECT *
FROM bi.dbo."accounts_table";

load min(Position_Date) as minDate resident tBalance;
varMinDate = peek('minDate');
load max(Position_Date) as maxDate resident tBalance;
varMaxDate = peek('maxDate');

refDate:
load date($(varMinDate)+rowno()-1) as Date
Autogenerate(num($(varMaxDate)-$(varMinDate))+1) ;
left join(refDate)

// Load Date, Position_Bal resident tBalance (not able to understand how this will work with the SQL select)
;
;

NoConcatenate
load Date, if(IsNull(Position_HoldingQty),peek('NewBalance'), Position_HoldingQty) as NewBalance, if(IsNull(Position_HoldingQty), peek('NewBalance'), Position_HoldingQty)-peek('NewBalance') as var resident refDate order by Date;

drop table refDate;