topic How can i use Peek Function here ? in QlikView

How can i use Peek Function here ?

sspawar88 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community,

Suppose i'm having CHECK_POINT_ID & DQ_TIME_ID1 two field in VOLUMETRIC table .

In code i use

LET vCurrDateData=peek('Max_DQ_Time',0,'VOLUMETRIC1'); &

LET vCurrDateData1=peek('Min_DQ_Time',0,'VOLUMETRIC2');

Here peek function will store only one value at a time but requirement is for getting Max & Second Max of DQ_TIME_ID1 for each CHECK_POINT_ID.

Max & Second Max for each CHECK_POINT_ID are different.

How can i get this?

VOLUMETRIC:

LOAD
DQ_TIME_ID AS DQ_TIME_ID1,
Date(ApplyMap('TIMEDQ',DQ_TIME_ID)) as LOAD_DATE,
ApplyMap('PHYSICALDATASOURCE',PHYSICAL_DATA_SOURCE_ID) as FILE_NAME,
CHECK_POINT_ID,
ROW_CNT
FROM '../VOLUME.qvd'(qvd);

VOLUMETRIC1:

LOAD

MAX(DQ_TIME_ID1) AS

Max_DQ_Time

RESIDENT VOLUMETRIC ;

LET vCurrDateData=peek('Max_DQ_Time',0,'VOLUMETRIC1');

drop table VOLUMETRIC1;

VOLUMETRIC2:

LOAD

MAX(DQ_TIME_ID1) AS Min_DQ_Time

RESIDENT VOLUMETRIC where DQ_TIME_ID1<$(vCurrDateData);

LET vCurrDateData1=peek('Min_DQ_Time',0,'VOLUMETRIC2');

drop table VOLUMETRIC2;

Re: How can i use Peek Function here ?

sujeetsingh — Mon, 16 May 2016 06:31:02 GMT

How can any one tell this directly just first understand the function its syntax and then try to use it as per your need.

Qlik Design Blog : Peek() vs Previous() – When ... | Qlik Community

Re: How can i use Peek Function here ?

Kushal_Chawda — Mon, 16 May 2016 06:42:45 GMT

VOLUMETRIC:

VOLUMETRIC1:

LOAD

MAX(DQ_TIME_ID1) AS Max_DQ_Time

MAX(DQ_TIME_ID1,2) AS Min_DQ_Time

RESIDENT VOLUMETRIC ;

LET vCurrDateData=peek('Max_DQ_Time',0,'VOLUMETRIC1');

LET vCurrDateData1=peek('Min_DQ_Time',0,'VOLUMETRIC1');

Re: How can i use Peek Function here ?

sspawar88 — Mon, 16 May 2016 06:57:47 GMT

Hi Kushal,

But requirement is when i click on Check Point id 2 it should give result as max DQ_TIME_ID 274 & Second Max 204 and

when i click on Check Point id 3 it should give result as max DQ_TIME_ID 304 & Second DQ_TIME_ID 274.

But as my understanding here peek function will store only 304 as max & 274 for second max.

What should i use here to get values for both Check Point id ?

CHECK_POINT_ID	DQ_TIME_ID
2	204
2	274
3	274
3	304
3	304

Re: How can i use Peek Function here ?

Kushal_Chawda — Mon, 16 May 2016 07:02:00 GMT

Do you need variable for each check point ID?

VOLUMETRIC:

VOLUMETRIC1:

LOAD CHECK_POINT_ID,

MAX(DQ_TIME_ID1) AS Max_DQ_Time

MAX(DQ_TIME_ID1,2) AS Min_DQ_Time

RESIDENT VOLUMETRIC

Group by CHECK_POINT_ID;

Re: How can i use Peek Function here ?

sspawar88 — Tue, 17 May 2016 03:32:30 GMT

Hi Kushal,

But above your Code will fail if we are having only one DQ_TIME_ID1 in database.

yes i want different variable for check point id's. but how can i do it in my script

Here i'm giving code for understanding the scenario. i jalso attached sample data.

please check the text file.

why this code will not capable for giving max & second max for each CHECK_POINT_ID where we select.

is this due to peek function i.e. it will store only one value at a time in variable. how can i rectify this code !!

can you please help for this !!

Re: How can i use Peek Function here ?

sspawar88 — Tue, 17 May 2016 03:56:52 GMT

Can u look over this. i just edited the details which u can understand !!

please check all trail posts

Re: How can i use Peek Function here ?

Kushal_Chawda — Tue, 17 May 2016 05:44:18 GMT

How much distinct check point id's are there in your actual data. creating the variable for each of them is not a good practice. Why you wanna create the Variable of Max and Second max for each check point id?

Above code will not fail if there is no Second max Id rather it will give you null value for Second max field which is correct.