topic Re: Next higher value available in QlikView

Next higher value available

Tue, 16 Sep 2014 14:33:33 GMT

Hi community,

I'm facing an issue when trying to calculate a waiting time referring to the next higher available value...

My data look as follows:

What I now would like to calculate in QV is the waiting time per step (within one order). That means the time difference between the start date of the step I'm looking at and the end date of the previous step.

Problem: in case the directly previous step has no end date I would like to look at the next higher end date available (within one order).

The result of the calculated waiting time should then look like this:

Any suggestions how to achieve this?

Many thanks in advance!

Tim

Re: Next higher value available

marcus_sommer — Tue, 16 Sep 2014 15:51:53 GMT

You could calculate the Waiting Time within a resident load in which you could sort your rows per order by statement. Then you could calculate the actual row-value with the row-value from a previous row per previous() or peek().

How to react by the missing values? Its difficult to say - if you have not many cases you could use peek() within a nested if-loop - if its more complex then it could be easier to remove these rows and connect these table with a key to your datamodel.

- Marcus

Re: Next higher value available

Wed, 17 Sep 2014 07:26:02 GMT

Hey Marcus,

thanks for your reply. I've already tried to play around with the functions you mentioned and it works more or less. However, I think the if-loop could bring me another step further.

Could you give me an example how a nested if-loop would look like in this case?

Thanks

Tim

Re: Next higher value available

marcus_sommer — Wed, 17 Sep 2014 08:01:55 GMT

Something like this, important is the correct order and that you could build a "small" logic to compare the actual row with the previous rows:

t1:

Load

if(rowno() = 1 or Order <> peek('Order') or len(Start) < 1, '',

Start - if(len(peek('Ende', - 1)) >= 1, peek('Ende', - 1),

if(len(peek('Ende', - 2)) >= 1, peek('Ende', - 2),

if(len(peek('Ende', - 3)) >= 1, peek('Ende', - 3),

if(len(peek('Ende', - 4)) >= 1, peek('Ende', - 4)))))) as [Waiting Time]

Resident t0 order by Order, Step, Start;

drop table t0;

Alternatively with two tables:

t1:

Load *, Order & '|' & Step as Key Resident t0 order by Order, Step, Start;

t2:

Load

Key, Order, Ende,

if(Order = peek('Order'), Start - peek('Ende') as [Waiting Time]

Resident t1 where len(Start) >= 1 order by Key;

drop fields Order, Ende From t1;

drop tables t0;

Maybe you will need some effort to find the correct syntax and logig but generally both approaches will work.

- Marcus