Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a data like this -
Response Time | X |
1 sec | 10 |
2 sec | 20 |
3 sec | 30 |
I need to find the value of X corresponding to minimum response time and the value of X corresponding to maximum response time. So my new table in Qlik looks like -
Can you please help me to find the expression to be used for this?
1 sec | 10 |
3 sec | 30 |
Hi, you can try approach like this:
data:
load * inline [
Response Time, Value
1, 10
2, 20
3, 30
];
NoConcatenate
main_temp:
load
min([Response Time]) as min,
max([Response Time]) as max
resident data;
LET vMinValue=peek('min',0,'main_temp');
LET vMaxValue=peek('max',0,'main_temp');
main:
load
*,
if([Response Time]=$(vMinValue), 'min', if([Response Time]=$(vMaxValue), 'max')) as ind
Resident data
where not isnull( if([Response Time]=$(vMinValue), 'min', if([Response Time]=$(vMaxValue), 'max')) );
drop tables data, main_temp;
//drop field ind from main;
But I made life easier by not loading response time as a string '1 sec', '2 sec'..., but just number. If you want to 'work' with these strings, instead of [Response Time], make a conversion to number with
num(SubField([Response Time], ' ',1)
@justISO Thanks for the quick response. This is for loading data with the min, max and corresponding value of X, right?
I already have data loaded like the first table in the question. Now I need the expression for having a table (like second table in my question) selected from charts.
Sorry, I am quite new to Qlik, that's why 🙂
My approach for min/max is calculating your needed result (second table) in Qlik Data load editor (in load script). As you already have your data loaded in app, I believe you want to get your result in report level (in sheet) so as one of options would be to add a Table object with 2 dimensions: Response time, and a bit modified X with expression:
=if(num(SubField([Response Time], ' ',1))=min(Total num(SubField([Response Time], ' ',1))), [X],
if(num(SubField([Response Time], ' ',1))=max(Total num(SubField([Response Time], ' ',1))),[X]))
So X will only show value if response time is min/max. To hide everything in between, just uncheck include null values:
Final result should look like this as you want:
Entirely front end solution
Dimension=
aggr(if(X=FirstSortedValue(total X,X) or X=FirstSortedValue(total X,-X),ResponseTime),ResponseTime)
Measure=
X