Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.

1 Solution

Accepted Solutions
Not applicable
Author

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;

View solution in original post

8 Replies
swuehl
MVP
MVP

Maybe define your variable like

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

Not applicable
Author

Thanks for the response but it is not working.

Not applicable
Author

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;

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Not applicable
Author

Try like below in the script with Lookup function:

T:

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

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

Thanks Kai Hilton-Jones. It worked.