Qliksense MinString function: How to find lowest alphabetic string ignoring case(case insensitive)?

[ol_dev]

Hi,

In our Qliksense app, we reply on the function MinString({1} [Dimension]) to find the first available value in a dimension in lowest alphabetic order, but there is a caveat to this solution. It seems Qliksense's string comparison with MinString/MaxString is case sensitive, and the upper case letters have lower ordinal values than lower case letters. For this reason, it appears 'A' < 'Z' and 'a' < 'z', but 'Z' < 'a'.

For instance, given a list of countries ValueList('Ukraine', 'Uruguay', 'USA', 'Uzbekistan'), we expect MinString will return Ukraine, but instead it returns USA, because uppercase letter 'S' appears to be lower than lowercase letter 'k'.

Is there a way to get around this limitation so we can get the first value from lowest alphabetic order with case insensitive string comparison? We tried to use lower() and upper(), but the results are undesirable as we cannot restore it back to the original string. Please help. 

[Vegar]

You could try to do MinString({1} upper([Dimension]) )

[ol_dev]

Hi,

This does not work, as we want it to return the lowest alphabetic value but as its original version, not as the uppercase version. 

[rwunderlich]

This would work I think.

=Pick(
WildMatch(
MinString(lower(ValueList('Ukraine', 'Uruguay', 'USA', 'Uzbekistan')))
,'Ukraine', 'Uruguay', 'USA', 'Uzbekistan')
,'Ukraine', 'Uruguay', 'USA', 'Uzbekistan')

-Rob

[ol_dev]

@rwunderlich 

Thanks for your response, yes it works for this example. Though in real application, there is no ValueList and the data comes from a dimension. For instance, we may have a dimension like 'Countries', and then it becomes: 

=Pick(WildMatch(MinString(Lower([Countries])), [Countries]), [Countries])

The issue though, is that we would like to display the value from MinString ignoring current selection(regardless of what values are currently being selected in dimension [Countries]). I tried something like this, and it does not work: 

=Pick(WildMatch(MinString(Lower({1} [Countries])), {1} [Countries]), {1} [Countries]) 

Do you know if there is a way I can make Pick and WildMatch work ignoring current selections?

[Or]

If you load both the original case and UPPER() or LOWER() case in your original Load table, you should be able to do this with FieldIndex and FieldValue, I believe.

Of course, at this point it'd probably be easier to just add a row number field and use Min() on that, fetching the corresponding FieldValue.

[Vegar]

You could try finding the minstring using Firstsortedvalue(). This will return the desired country name in it its original case.

FirstSortedValue(Country, aggr(-Rank(lower(Country)), Country))

[Vegar]

You could also do a set analysis approach, that will give you the desired country. 

only({<
Country={"=Upper(Country)='$(=Minstring(upper(Country)))'"}
>} Country)

[ol_dev]

Hi,

The solutions you propose do work when no value from [Countries] are being selected, but stops working as soon as there is one value being selected. The idea is that we would like to find the MinString value ignoring current selections in the field. 

[Or]

I'm not sure why it would stop working when a selection is made. Ultimately the order of countries is fixed, and the list of countries is fixed. You could just pre-sort your countries in use Dual(), for that matter, with the corresponding row number.