topic Re: Filling missing values with previous data (File attached) in QlikView

Filling missing values with previous data (File attached)

Mon, 24 Jun 2013 15:20:20 GMT

Hello,

This is what I want to do:

I want to show the total stock in the past months. The stock is calculated using the current stock level with transaction history.

Problem is, when a month has no transactions, the stock level is 0. When calculating the total stock in that month, the total will be inaccurate.

Can someone please look at my Qlikview file? I have been struggling with this for the past 2 days.

Thanks in advance.

Re: Filling missing values with previous data (File attached)

simondachstr — Mon, 24 Jun 2013 15:51:37 GMT

Maybe working with Full Accumulation would help in this case? This way, when there's no transaction the value will stay the same.

Re: Filling missing values with previous data (File attached)

Mon, 24 Jun 2013 17:46:13 GMT

But then all the values are accumulated...

Re: Filling missing values with previous data (File attached)

hic — Tue, 25 Jun 2013 05:21:58 GMT

You should probably do this in the script using the peek() function. See more on

http://community.qlik.com/docs/DOC-3786 and

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field

HIC

Re: Filling missing values with previous data (File attached)

parthiband — Tue, 25 Jun 2013 05:44:00 GMT

The Same Problem which i faced in last week..

Its very simple..

follow the link which Henric Cronstrom gave

Works fine

Re: Filling missing values with previous data (File attached)

Tue, 25 Jun 2013 06:33:33 GMT

I tried this numerous times.. the problem is, my calculations get all messed up and they dont work anymore. Besides, I have like 500 different types of articles, generating a timespan of 4 years for every date would take forever. Is this possible maybe once a month?

Re: Filling missing values with previous data (File attached)

Anonymous — Tue, 25 Jun 2013 07:43:04 GMT

use the

above( [ total ] expression [ , offset [,n ]] ) function it helps

Re: Filling missing values with previous data (File attached)

Tue, 25 Jun 2013 07:52:09 GMT

Can you specify this? Where should I use it?

Re: Filling missing values with previous data (File attached)

Tue, 25 Jun 2013 09:11:09 GMT

I have the following code:

TempTable_Rates:

Load [Date],

[KeyPart]

Resident [Stock];

MinMaxDate:

Load Min(Date) as MinDate, Max(Date) as MaxDate resident TempTable_Rates;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;

Drop Table MinMaxDate;

Join (TempTable_Rates)

Load Date(recno()+$(vMinDate)) as [Date] Autogenerate vMaxDate - vMinDate;

[FinalStock]:

NoConcatenate Load

date([Date]) as [Date],

[KeyPart]

Resident TempTable_Rates;

RIGHT JOIN

LOAD

[Date],

[KeyPart],

[PartStockRecord],

If( IsNull( [MutationValue] ), Peek( [MutationValue] ), [MutationValue] ) as [MutationValue],

[PartStock],

[PartStockStart],

[PartStockEnd],

[CodeGroep]

Resident [Stock]

ORDER BY [KeyPart], Date DESC;

Drop Table TempTable_Rates;

Drop Table [Stock];

But it does not join the tables. It just shows an empty list of dates before showing the actual values. What am i doing wrong?

Re: Filling missing values with previous data (File attached)

Anonymous — Tue, 25 Jun 2013 13:55:22 GMT

Please look at the attached file

In the expressions tab I have used the following expression

=if(sum(cost)='0',Above (sum(cost)),sum(cost))

Regards

Harsha

Re: Filling missing values with previous data (File attached)

Tue, 25 Jun 2013 14:23:49 GMT

I have like this:

=if(sum(aggr( FirstSortedValue( PartStockEnd, -Date) , Month, KeyPart))='0',Above(sum(aggr( FirstSortedValue( PartStockEnd, -Date) , Month, KeyPart))),sum(aggr( FirstSortedValue( PartStockEnd, -Date) , Month, KeyPart)))

But it shows just the same values.

and i think i know what the problem is: the transaction doesnt have records. so its not even 0, there is no record.