topic How can I use peek function to find the specific value? in QlikView

How can I use peek function to find the specific value?

Wed, 01 Jan 2014 21:54:02 GMT

I want to use a variable where the value returned is specific field value based on specific field value of same table. can anyone help me thanks.

FOR EX:

A B C

1 a

2 b

3 b

4 j Y

5 k

6 l

7 i

8 u

So I need a variable that goes through Column C and read only Y and get the value from column A which is 4.

Re: How can I use peek function to find the specific value?

swuehl — Wed, 01 Jan 2014 22:39:29 GMT

Maybe define your variable like

=only({<C = {Y}>} A)

Re: How can I use peek function to find the specific value?

Wed, 01 Jan 2014 22:52:25 GMT

Thanks for the response but it is not working.

Re: How can I use peek function to find the specific value?

Wed, 01 Jan 2014 22:57:15 GMT

The peek() function is only available in the script. If indeed you want to populate the variable during the execution of the script then try something like this:

// Load the data
OriginalData:
LOAD
     A,
     B,
     C
FROM (Your Data Source);

// Filter where C=Y; assumption is that there is only a single row where C=Y
FindValue:
LOAD
     A
resident OriginalData
where C = 'Y';

// Populate variable
let vVariable = peek('A', -1, 'FindValue');

// Drop temp table
DROP TABLE FindValue;

Re: How can I use peek function to find the specific value?

Peter_Cammaert — Thu, 02 Jan 2014 00:11:33 GMT

Even shorter (and slightly better to understand) when you use the QlikView analog of a vertical lookup:

Data:

LOAD * INLINE [
Type, Price, Amount
A, 1000, 50
B, 500, 100
C, 80, 6500
D, 9999, 2
E, 19999, 1
F, 66, 1234
];

LET vPrice = Lookup('Price', 'Type', 'E');
LET vAmount = Lookup('Amount', 'Type', 'E');

The Lookup() function cannot be used in chart expressions though. And that's a pity...

Peter

Re: How can I use peek function to find the specific value?

Thu, 02 Jan 2014 00:56:53 GMT

Try like below in the script with Lookup function:

LOAD * INLINE [

A, B , C

1 , a ,

2 , b,

3 , b,

4 , j , Y

5 , k,

6 , l,

7 , i,

8 , u,

];

LET vAC = Lookup('A' , 'C' ,'Y', 'T') ;

LET vBC = Lookup('B' , 'C' ,'Y', 'T') ;

Re: How can I use peek function to find the specific value?

Thu, 02 Jan 2014 09:33:11 GMT

I was advised to avoid using Lookup() - especially with larger datasets - because it is slow compared to a resident load and peek(). I use it all the time now. I'll try and dig out the information.

Re: How can I use peek function to find the specific value?

Thu, 02 Jan 2014 10:07:38 GMT

Update: I just tested both methods with a large amount of data and Lookup() is slightly faster. The advice I got was for Version 9 so maybe it is related to that; either that or the advice I was given was wrong. Please ignore my comment above.

Re: How can I use peek function to find the specific value?

Thu, 02 Jan 2014 16:23:47 GMT

Thanks Kai Hilton-Jones. It worked.