Peek() in script?

[#1]

Hi

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

[#2]

Hi Sravan,

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

Thank you!

Rainer

[ToniKautto]

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

[#4]

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

[#5]

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..

[#6]

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

[#7]

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

[#8 · Accepted solution]

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

[#9]

Hi Rainer,

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

Thanks a lot

Sravan