topic Re: peek function in QlikView

peek function

maarcus_smith — Thu, 04 Nov 2021 13:11:10 GMT

hi,

im using the peek function to grab the max date of the previous order and comparing this against the min date of the next order. it seems to work for some orders but not for others. im grouping my data by order, part and line, in a previous table, then im resident loading that table in again to get the "ChangeoverTime"to give the user flexibility if they want to select a specific part or line.

my code is:

final1:

min(SGRP.MinDate) as Min.Id, max(SGRP.MaxDate) as Max.Id
order, line , part
from x
group by order, line , part

final2:

Min.Id - peek('Max.Id') as ChangeoverTime,
Min.Id, Max.Id, PART, order, line.

resident final1

drop table final1;

what i want from my output is that a user can select a line and/or part, and it will show them all the changeover time from the min of one batch, to the max of the previous batch for that part or line.

this is what im getting:

as you can see, most of the lines are correct, however the highlighted one is wrong. the changeover time should be null for this field as there is no previous order on that line, however, its taking the max id of the order on line 10 for some reason.

how do i correct this to where for the first order on each line or part, the changeover is null?

many thanks

Re: peek function

rwunderlich — Thu, 04 Nov 2021 14:54:36 GMT

If(line = peek('line'), Min.Id - peek('Max.Id'), null()) as ChangeoverTime

-Rob

Re: peek function

maarcus_smith — Thu, 04 Nov 2021 15:35:43 GMT

@rwunderlich hi rob thanks for answering me.

ive tried updating my peek function with your adivce, however it doesn't seem to have the desired affect, it makes some of my changeover times nulls but then there are still some minus'. however there are minus' not because of the line changing and i dont understand why, see the highlighted rows

Re: peek function

marcus_sommer — Fri, 05 Nov 2021 12:03:33 GMT

You need also to add a proper order by statement to your resident-load to ensure that the order of the records are sorted in your intended way.

- Marcus