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. 

[ol_dev]

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. 

[Vegar · Accepted solution]

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

[Vegar]

@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),','))

    )

[marcus_sommer]

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;

[ol_dev]

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.