Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Maybe define your variable like
=only({<C = {Y}>} A)
Thanks for the response but it is not working.
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;
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
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') ;
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.
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.
Thanks Kai Hilton-Jones. It worked.