Question on table expressions

[VeenaVijayakumar]

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

[justISO]

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)

[VeenaVijayakumar]

@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 🙂

[justISO]

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:

[vinieme12]

Entirely front end solution

Dimension= 

aggr(if(X=FirstSortedValue(total X,X) or X=FirstSortedValue(total X,-X),ResponseTime),ResponseTime)

Measure=

X