topic Return Dimension based on Lowest Value in App Development

Return Dimension based on Lowest Value

simoncarts — Tue, 12 Jul 2022 09:33:04 GMT

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?

Re: Return Dimension based on Lowest Value

Or — Tue, 12 Jul 2022 09:38:37 GMT

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))

Re: Return Dimension based on Lowest Value

simoncarts — Tue, 12 Jul 2022 09:47:48 GMT

Thanks for the quick response!

FirstSortedValue does work and I'm going to use this as my solution. How does it behave when you have 2 Rates being equally the lowest? Ideally I'd like the Manufacturer & Vehicle Descriptions listed if there is more than one 🙂

The Expression you've written out still returns the Nested Aggregation error

Re: Return Dimension based on Lowest Value

Or — Tue, 12 Jul 2022 11:13:41 GMT

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))