Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good morning All,
I'm trying to create the equivalent of a VLOOKUP / INDEX & MATCH in Qlik where the [Manufacturer] & [Vehicle Description] for the Lowest [Rate] is returned
When I hard-code the lowest [Rate] value into the Expression, the app returns the desired result(s) with no errors (expression below)
Concat(DISTINCT if([Rate]=(137.56), [Manufacturer] & ' ' & [Vehicle Description]),Chr(10))
When I only replace the hard-coded rate with Min([Rate]) (expression below)
Concat(DISTINCT if([Rate]=(Min([Rate])), [Manufacturer] & ' ' & [Vehicle Description]),Chr(10))
I receive an Error stating 'Error in expression: Nested aggregation not allowed'
I've tried placing Aggr in different parts of the expression which removes the error but then returns no results.
Any ideas?
Perhaps try using FirstSortedValue() instead?
You might also be able to get a result using an internal aggr based on the two dimensions in question, assuming each combination is limited to having a single rate. It'd look something like:
Concat(DISTINCT if([Rate]=(Min(aggr(Only([Rate]),Manufacturer,[Vehicle Description])), [Manufacturer] & ' ' & [Vehicle Description]),Chr(10))
Perhaps try using FirstSortedValue() instead?
You might also be able to get a result using an internal aggr based on the two dimensions in question, assuming each combination is limited to having a single rate. It'd look something like:
Concat(DISTINCT if([Rate]=(Min(aggr(Only([Rate]),Manufacturer,[Vehicle Description])), [Manufacturer] & ' ' & [Vehicle Description]),Chr(10))
Right, the outer min() will still cause the nested aggregation issue. Didn't notice that since I didn't catch the outer concat(). You could try using set analysis instead of the nested if(), something along the lines of:
concat({< Rate = {"$(=Max(Rate))"} >} [Manufacturer] & ' ' & [Vehicle Description]),Chr(10))