Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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.

Tags (1)
1 Solution

Accepted Solutions
Not applicable

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

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;

8 Replies
swuehl
Not applicable

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

Maybe define your variable like

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

Not applicable

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

Thanks for the response but it is not working.

Not applicable

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

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
Not applicable

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

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

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

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

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

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

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

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

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

Thanks Kai Hilton-Jones. It worked.