topic Re: Peek() in script? in QlikView

Peek() in script?

Wed, 18 May 2011 09:22:13 GMT

I have a table like the following

The counterRdgDifference (Counter reading Differences) are coming directly from the database and sometimes those are not calculated as in the case above.

In such case, I need to calculate the counterRdgDifference in Qlikview.

My assumption is using peek in script. I dont know how to do this in expression.

My idea in script:

MeasurementTable:

Load *,

if(DifferenceReading= 0, MeasuringReading-Peek('MeasurementTable',-1) as DifferenceReading

;

Load MeasurementDoc,

Measuringpoint_Key,

MeasuringUnit_Key,

Date_Key,

ReadingDateTime,

MeasRdg_READG_CHGD * Conversion as MeasuringReading,

Difference_CDIFF_CHGD * Conversion as DifferenceReading,

OBJNR_Key,

MeasPosition_PSORT,

Description_PTTXT,

MeterType,

Description_ATBEZ

Resident TableX

;

Is it correct or should I do any aggregation in script based on Measuring point and Measuring Doc??

Thanks

Peek() in script?

Wed, 18 May 2011 11:37:09 GMT

Hi Sravan,

please provide a short set of sample data I can work with.

Thank you!

Rainer

Re: Peek() in script?

ToniKautto — Wed, 18 May 2011 12:30:39 GMT

Please see QlikView manual (F1 in desktop client) and lookup Peek for guidance and you will find that the function definition.

peek fieldname [ , row [ , tablename ] ] )

So in your example above you are using unsuitable parameters. The first param must be a fieldname, hence you can not use the table name as you have done. Also notice that when using peek in a load, you can not use table since the table does not yet exist hence QlikView assumes the current table in such case. The IF statement needs to have an ELSE cause in order to add values when it is not already 0.

if(DifferenceReading= 0, MeasuringReading-Peek('MeasuringReading'), DifferenceReading) as DifferenceReading

Also you might want to consider that first row will not be able to Peek() since there is no previous row.

if(DifferenceReading=0 AND RecNo() > 1, MeasuringReading-Peek('MeasuringReading'), DifferenceReading) as DifferenceReading

Re: Peek() in script?

Wed, 18 May 2011 12:31:37 GMT

HI Rainer,

Thanks for ur answer.

I am still trying but the application is attached now.

Thanks

Sravan

ooops.. I am not getting how to attach a .qvw application:( Any help??

Got worked..

Regards

Sravan

Re: Peek() in script?

Wed, 18 May 2011 12:42:02 GMT

Hey Toni,

I did exactly what you said and yes I know the syntax of Peek function..As you see I get the negative values and also DifferenceReading cannot be named same..

Re: Peek() in script?

Wed, 18 May 2011 14:26:06 GMT

Hi Sravan,

so far I see you have to sort the table by Measuring_PointKey and Date_Key and this will end up in a line like the following:

if(IsNull(OnHandBalance) and Item_number=peek(Item_number),peek(OnHandBalance),OnHandBalance) as OnHandBalance,

or with your fields:

if(IsNull(DifferenceReading) and Measuring_Key=peek(Measuring_Key),MeasuringReading-Peek('MeasuringReading'),DifferenceReading) as DifferenceReading,

Attached are the application this line comes from.

Hope it helps.

Rainer

Re: Peek() in script?

Wed, 18 May 2011 15:20:56 GMT

Hi Rainer,

Thanks for the example. I did exactly the same way, still it does not shows the differences.

I wonder what is happening. Attached is what I did.

Thanks in advance

Sravan

Re: Peek() in script?

Thu, 19 May 2011 07:40:11 GMT

Good morning Sravan,

I hope we are a step closer solving your requirement.

Find the right sort order is the key.

See the attached example. I hope it´s what you are looking for.

Good luck!

Rainer

Re: Peek() in script?

Thu, 19 May 2011 08:01:12 GMT

Hi Rainer,

That really did the trick bringing Datekey before in the Order by statement.

Thanks a lot

Sravan