Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Loading a balance table missing daily records

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,

5 Replies
erich_shiino
Honored Contributor

Re: Loading a balance table missing daily records

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;

Not applicable

Loading a balance table missing daily records

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;

gandalfgray
Valued Contributor

Loading a balance table missing daily records

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

continuosgraph.JPG

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"

lineplateu.JPG

and set the dimension axis to "Continuos":

continuos.JPG

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

];

gandalfgray
Valued Contributor

Re: Loading a balance table missing daily records

One more important thing I forgot to mention,

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

fullaccumulation.JPG

Not applicable

Loading a balance table missing daily records

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;

Community Browser