Next higher value available

[#1]

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

[marcus_sommer]

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

[#3]

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

[marcus_sommer]

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