topic Re: Qliksense MinString function: How to find lowest alphabetic string ignoring case(case insensitive)? in App Development

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

ol_dev — Mon, 14 Apr 2025 17:53:42 GMT

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.

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

Vegar — Mon, 14 Apr 2025 18:03:28 GMT

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

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

ol_dev — Mon, 14 Apr 2025 18:13:02 GMT

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.

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

rwunderlich — Mon, 14 Apr 2025 19:08:53 GMT

This would work I think.

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

-Rob

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

ol_dev — Mon, 14 Apr 2025 19:21:54 GMT

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

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

Or — Mon, 14 Apr 2025 19:34:01 GMT

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.

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

Vegar — Mon, 14 Apr 2025 19:45:43 GMT

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

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

Vegar — Mon, 14 Apr 2025 19:47:31 GMT

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

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

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

ol_dev — Mon, 14 Apr 2025 19:50:30 GMT

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.

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

Or — Mon, 14 Apr 2025 20:24:59 GMT

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.

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

ol_dev — Mon, 14 Apr 2025 20:31:51 GMT

Hi,

Tbh I do not know why either, but Qliksense behaves this way for functions such as MinString. For instance, if no country is selected, MinString will return the first item from the lowest alphabetic order in Dimension [Countries]. If values are selected, then it will search only among the selected values. In the worst case, if one value is selected, it will return just the one selected value, basically becomes useless.

To get around this issue, I was told to prepend {1} to MinString's argument(ie. MinString([Countries]) becomes MinString({1} [Countries])), and it did the trick for me. However, the same solution does not work anymore when using a cascade of function calls, ie. Pick(WildMatch(MinString(Lower({1} [Countries])), {1} [Countries]), {1} [Countries]) does not work at all.

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

Vegar — Tue, 15 Apr 2025 05:56:15 GMT

You are right, the expressions I gave you where designed to work with the current selections, but you could manipulate them to work with the full set (ignoring your selections).

In the screenshot I kept the original expressions, but in the second column the expressions ignores any selections made.

The SET analysis method

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

The First Sorted Value method

FirstSortedValue({1}Country, Aggr(-Rank(only({1}Lower(Country))), Country))

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

Vegar — Tue, 15 Apr 2025 06:31:07 GMT

@ol_dev

You will have some trouble using wildmatch like that, it expect comma separated values. You could however try this adjusted approach.

=Pick(

MixMatch( '$(=MinString({1} Lower([Country])))',

$(=Concat({1}chr(39)&[Country]&chr(39),','))),

$(=Concat({1}chr(39)&[Country]&chr(39),','))

)

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

marcus_sommer — Tue, 15 Apr 2025 10:29:45 GMT

What about transferring the essential part of the logic into the data-model. Here a starting-point:

t1: load * inline [
Country
Ukraine
Uruguay
USA
Uzbekistan];

t2: load dual(Country, rowno()) as CountryNew, rowno() as RowNo
resident t1 order by Country;

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

ol_dev — Tue, 15 Apr 2025 15:33:31 GMT

The solutions work, thx. I think the issue I am having is basically a combination of trying to get around the limitation of MinString and the current selection, I appreciate that you took the time and trouble to help me figure out.