topic Problem: Expression generating negative numbers in QlikView

Problem: Expression generating negative numbers

Wed, 06 Jun 2012 09:06:14 GMT

Good Morning, I have a problem where I am trying to extract meter readings from our system. This example is an approximation of the data generating the problem.

READ DATE

297 01.04.2012

9998 01.03.2012

The true difference between the two figures is -9701. However the true system difference is 299, the meter rolls back to 0 when it goes past 9999. Our system calculates this automatically but im unable extract this as its a calculated table (or structure)

I am using this Expression at the moment (which is giving the erroneous data) :- FirstSortedValue(Meter_Reading,-Meter_Reading_Date)-FirstSortedValue(Meter_Reading,-Meter_Reading_Date,3)

Thanks,

Paul.

Problem: Expression generating negative numbers

christophebrault — Wed, 06 Jun 2012 09:29:17 GMT

Hi,

And if you try :

fabs(FirstSortedValue(Meter_Reading,-Meter_Reading_Date)-

(FirstSortedValue(Meter_Reading,-Meter_Reading_Date,3)-9999))

Problem: Expression generating negative numbers

Wed, 06 Jun 2012 10:01:44 GMT

Christopher, Thank you very much for that! It's so simple when I look at it. It nailed the spot.

Take care.

Paul.

Problem: Expression generating negative numbers

Wed, 06 Jun 2012 14:56:40 GMT

One minor problem I have, this works well when negative numbers when a number starts before and including 9999 and then rolls over to 0+, the problem is when the number moves from 350 to 375 for example, I need the formula in the expression to deal with both, any ideas please?

Problem: Expression generating negative numbers

Wed, 06 Jun 2012 15:10:13 GMT

ive tried :-

if(FirstSortedValue(Meter_Reading,-Meter_Reading_Date)-FirstSortedValue(Meter_Reading,-Meter_Reading_Date,3)<'0',

fabs(FirstSortedValue(Meter_Reading,-Meter_Reading_Date)-

FirstSortedValue(Meter_Reading,-Meter_Reading_Date,3)-9999)) which helps for the neg numbers, I need help to get this expression to deal with positive numbers 0 and greater to be calculated in same expression.

Re: Problem: Expression generating negative numbers

swuehl — Wed, 06 Jun 2012 15:46:17 GMT

Well, I think you can just reuse your original expression for the 'standard case', can't you?

Or create a aggregated READ value in the script like

LOAD *

, rangesum(if(READ<peek(READ),10000),peek(AggrREAD),READ-peek(READ)) as AggrREAD

INLINE [

READ, DATE

9998, 01.03.2012

297, 01.04.2012

350, 01.05.2012

8000, 01.01.2014

100, 01.01.2015

2222, 01.02.2016

0, 01.03.2020

];

So you don't need to bother about crossing the 9999 border anymore after.

Regards,

Stefan

edit: this needs an ordered table to work correctly, and if you want to start with your first READ value and not zero as AggrREAD, you could use:

LOAD *,

if(isnull(peek(READ)), READ,rangesum(if(READ<peek(READ),10000),peek(AggrREAD),READ-peek(READ))) as AggrREAD

INLINE [

...