How can i use Peek Function here ?

[sspawar88]

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;

[sujeetsingh]

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

[Kushal_Chawda]

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

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');

[sspawar88]

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

[Kushal_Chawda]

Do you need variable for each check point ID?

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 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;

[sspawar88]

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 !!

[sspawar88]

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

please check all trail posts

[Kushal_Chawda]

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.