topic Re: Dealing with a table in QlikView

Dealing with a table

david_ze — Sun, 24 Feb 2013 22:00:31 GMT

Hi all,

I'll appreciate your promptly help on a issue I have:

I have Customer table with Date and Amounts which looks like this:

Date Account Amount

31/01/2012 101 1,000

31/01/2012 103 1,500

28/02/2012 103 900

31/03/2012 101 500

30/04/2012 103 300

30/04/2012 101 0

See that for account '101' I have no Amount for the date 28/2/2012, and
for account '103' I don't have amount for 31/03/2012.

What I would like to do is to write a script that aggregates the account for each month.

In case that there is no amount for
account on a certain month than we should take the amount from the previous
month.

means that the table should look like this:

31/01/2012 101 1,000

31/01/2012 103 1,500

28/02/2012 101 1,000 added

28/02/2012 103 900

31/03/2012 101 500

31/03/2012 103 900 added

30/04/2012 103 300

30/04/2012 101 0

Than to do the aggregation for each month..

01/2012 2,500

02/2012 1,900

03/2012 1,400

04/2012 300

I'll appreciate you'r help on this.

Best

David

Re: Dealing with a table

phaneendra_kunc — Sun, 24 Feb 2013 23:37:25 GMT

May be this?

Data:

LOAD * INLINE [

Date, Account , Amount

01/2012,101, 1000

01/2012,103, 1500

02/2012,103, 900

03/2012,101, 500

04/2012,103, 300

04/2012,101, 0

];

//Add all months to the Data..

Month_Account:

Load distinct Account Resident Data;

left join

Load * Inline [

Date

01/2012

02/2012

03/2012

04/2012

];

Month_Account_NEW:

NoConcatenate

Load * Resident Month_Account;

left join

Load * Resident Data ;

Drop Tables Month_Account,Data;

Final:

NoConcatenate

Load Date,Account,

IF (Account=PEEK('Account') AND isnull(Amount),PEEK('NEW_VAL'),Amount) AS NEW_VAL

Resident Month_Account_NEW order by Account Desc;

Drop Table Month_Account_NEW;

Re: Dealing with a table

david_ze — Sat, 02 Mar 2013 06:02:05 GMT

Thakns. It works great.

David