topic Re: Peek Value changes per ID issue in QlikView

Peek Value changes per ID issue

ngosz4074 — Mon, 18 Jul 2016 15:10:48 GMT

Hello,

I'm having some issues trying to get an interval of a value that I want to reset when the Customer ID changes in my table.

An example would be

ID RESULT_TIME VALUE INTERVAL

1 11/1/2013 4:42 PM 4.4 0

1 11/1/2013 4:54 PM 8.1 0:12

1 11/1/2013 8:54 PM 9.9 4:00

2 7/18/2016 7:36 AM 4.8 0

2 7/18/2016 11:45 AM 12.3 4:09

Here is my script - in Tab2 piece is where I'm trying to figure out where to peek at the value based on the time. Then reset upon the ID changing.

Tab1:

LOAD

CUST_ID,

[CUST_RESULT_TIME] as [RESULT_TIME],

[ALL_VALUE] as [VALUE]

RESIDENT ALL_CUST_RESULTS;

Tab2:

LOAD * ,

peek([RESULT_TIME]) as peek_val1,

peek ([CUST_ID]) as CUST_PEEK (this is where I'd like to change the Peek value per ID)

RESIDENT Tab1;

Drop table Tab1;

Tab3:

LOAD *,

Interval( peek_val1 - [RESULT_TIME], 'hh:mm') as IntervalDiff

RESIDENT Tab2;

Drop table Tab2;

CUST_INTERVALS:

Load *,

If ( [RESULT_TIME] > peek_val1, 'Y','N') as PreviousValueFlag,

If(IntervalDiff > '-32:01' and IntervalDiff < '0', 'Y','N') as IntervalRangeFlag

Resident Tab3;

Drop table Tab3;

What piece am I missing to complete this task?

Thanks in advance

Re: Peek Value changes per ID issue

effinty2112 — Mon, 18 Jul 2016 15:41:16 GMT

Hi Neal,

I've not tested this but try:

Tab1:

LOAD

CUST_ID,

[CUST_RESULT_TIME] as [RESULT_TIME],

if(CUST_ID = Peek('CUST_ID'), [CUST_RESULT_TIME] - Peek([RESULT_TIME]) ,0) as INTERVAL,

[ALL_VALUE] as [VALUE]

Cheers

Andrew

Re: Peek Value changes per ID issue

ngosz4074 — Tue, 19 Jul 2016 15:51:14 GMT

Hi Andrew,

Thanks for replying. I get a 0 back for the results when I tried this code. So, I added an interval ( ) function wrapping the calculation and I get a '-' per CUST_ID change.

Thanks

Neal

Re: Peek Value changes per ID issue

ngosz4074 — Tue, 19 Jul 2016 19:02:36 GMT

Here's an additional piece of the desired final result:

ID RESULT_TIME PEEK_TIME VALUE INTERVAL

1 11/1/2013 4:42 PM 0 4.4 0

1 11/1/2013 4:54 PM 11/1/2013 4:42 PM 8.1 0:12

1 11/1/2013 8:54 PM 11/2/2013 4:54 PM 9.9 4:00

2 7/18/2016 7:36 AM 0 4.8 0

2 7/18/2016 11:45 AM 7/8/2016 7:36 AM 12.3 4:09

Re: Peek Value changes per ID issue

maxgro — Tue, 19 Jul 2016 22:04:39 GMT

maybe this

SET TimestampFormat='M/D/YYYY h:mm tt[.fff]';

SET ThousandSep=',';

SET DecimalSep='.';

😧

load rowno() as rowno, ID, RESULT_TIME, VALUE inline [

ID ; RESULT_TIME; VALUE; INTERVAL

1 ; 11/1/2013 4:42 PM; 4.4 ; 0

1 ; 11/1/2013 4:54 PM ; 8.1 ; 0:12

1 ; 11/1/2013 8:54 PM ; 9.9 ; 4:00

2 ; 7/18/2016 7:36 AM ; 4.8 ; 0

2 ; 7/18/2016 11:45 AM ; 12.3 ; 4:09

] (delimiter is ';')

;

D1:

load

ID,

RESULT_TIME,

VALUE,

interval(if(ID <> Peek('ID'), 0, RESULT_TIME - Peek('RESULT_TIME'))) as NEW_INTERVAL

Resident D

Order By rowno;

DROP Table D;