Bug in comparing strings when using <= operator

[#1]

I just sent this bug request to support@qlik.com. Let's see what's coming up:

Hello!

I have a problem concerning string comparison and the <= operator. Using >= works as expected.

Tested in Executive Dashboard example file on field City.

Situation:

Make a new sheet.

Display the City- field as a list box.

Construct 2 variables: vFrom and vTo

Display the variables in a input box.

Create a macro.

CopyPaste this code into the module:

sub SearchBetween
 set f = ActiveDocument.Fields("City")
 f.Select ""
 vFrom = ActiveDocument.Variables("vFrom").GetContent().String
 vTo = ActiveDocument.Variables("vTo").GetContent().String
 f.Select "= City >= '"& vFrom & "' and City <= '" & vTo & "'"
end sub

Create a button that runs the code.

Type in the variable values as follows:

vFrom = A*
vTo = C*

Press button.

The expected result should be that all works starting with letters A, B and C should be selected.

The result however is: words starting with letters A and B are selected, NOT C.

Clearly, a bug when handling the <= operator.

[flipside]

Hi,

What is happening here is that the wildcard character is returning ascii code 42 which is less than the ascii 65 (capital letter A), hence it would only return a city value of 'C' if one existed.  If you test this with a city value of C*, this is selected by your code.  I don't think you can use a wildcard search string in a "less than" search in Qlikview this way, so this is probably not a bug.

What you need to do is replace the '*' character with a code higher than ascii code 122 (lower case z) or maybe a code in the extended set if you need to check for these characters.  This will fool the user into thinking they are submitting a wildcard, when actually they are not.

flipside

[#3]

Thank you for your reply.

I can now see why this is not a bug. I think I have to write a replace statement, that checks for the wildcard in the "to" variable and replaces the char with something else (e.g. 'ö', that is the last letter of the alphabet used here).

Since I assumed the select method of texts in macros works the same way as in the text boxes, I was a little bit confused over the result and assumed it to be a bug.

Good to have this clarification registered.

Greetings,

Sören

[Miguel_Angel_Baeyens]

Hi Sören,

Did you try using text operators "follows" and "precedes" instead of numeric operators ">=" and "<="?

If(TextField follows 'B*' AND TextField precedes 'E*', TextField)

That will return all values in TextField starting on C to those starting on D, irrespective the length of the value.

Hope that helps.

Miguel

[#5]

Hi Miguel,

Thanks for the hint, but this is not working as you suggested.

I have a QlikView file with the following load * inline content:

LOAD * INLINE [
    Text, Data
    a, 1
    an, 1
    as, 1
          bee, 1
          before, 1
          begun, 1
    city, 1
    comparison, 1
    data, 1
    degree, 1
    dynasty, 1
    effort, 1
    ergonomy, 1
    fastfood, 1
];

I create the variables vFrom and vTo and display them in an input box.

I create a chart as a straight table and put the following as a calculated dimension:

=IF(Text follows '$(vFrom)' and Text precedes '$(vTo)',Text)

and this as the expression:

=Data

In the vFrom I put: A*

In the vTo I put: C*

I then expect (as one could expect from the expression in the dimension), that I should get the list:

a
an
as
bee
before
begun
city
comparison

but instead I get

an
as
bee
before
begun

I.e. the first 'a' is left out (because the filter looks at the text and expects something other than just 'a') and all the C (because the comparison expects somehting with ascii number less than 'a').

Conclusion:

'follows' and 'precedes' works as >= and <=

Since, when I input

vFrom = A

vTo = Cz

then I get all the words that starts with a,b and c.

The situation from the beginning was that I want to be able to search words within a range and do a selection in the data according to these words. I have a database table with some hundreds of thousands of rows, where there is a text field that is of interest. I am splitting the strings with subfield(<fieldname>, ' ') operator and puts the words in it table of its own. I then link this table to the rest of the data and then I have a list of just words to search in and select from. I want to be able to create a nice way of searching and selecting data for the users. The problem is that I have not found an intuitive way in QlikView to create the search functionality.

Work around:

Still using the macro approach and simply putting a 'z' after the vTo variable's text inside the macro code, seems to be the only way around this.

Sören

[swuehl]

Maybe with a little helper (maxstring / minstring)?

Please check attached.

Regards,

Stefan

[#7]

Thanks for this idea, swuehl! It's closing up, but unfortunately this is not exactly what I expected.

Try inputting

vFrom = ba*

vTo = bu*

should return all words beginning with 'b', since the second letter of thevFrom variable is less than second letter in 'bee' word and second letter in vTo variable is greater than second letter in 'begun' (the last word). I have no idea why the set analyzis in this case fails... But good thinking.

Sören

[#8]

I combined these ideas to a - for me - working solution, that I attached.

The solution was to

1. Create a list box with the following expression:

   =if(Text >= '$(vFrom)' and Text <= '$(vTo)' & chr(1000), Text)

   and have this list box either hidden (have not tried that yet) or at least minimized not to confuse user
2. Create a macro that reads the possible values in the list with expression and selects these values:

   sub SearchBetween
   rem CHECK THE ID OF THE SHEET OBJECT FOR THE RIGHT ONE!
   set table = ActiveDocument.GetSheetObject("LB02")
   table.Clear
   table.selectPossible
   end sub

3. Create a button that runs this macro

One of the tricky parts was to figure out the addendum to vTo- variable inside the expression. This is needed to select e.g. 'from=b and to=b' to get all that starts with 'b'. It is now set to chr(1000) and that's enough for our needs at the moment.

This will be interesting to try out on live data to get the idea if this can be a workign solution for bigger data sets

Thanks for all the good ideas!

Sören

P.S. I could not get this to work only in macro, since the largest ascii number that vbscript can take is 255. That was somehow not enough to select the Swedish word 'början'. Therefore chr(1000) in the list.

[swuehl]

I don't like Macros to do something like this

I think you just need to check if the maxstring / minstring finds a match in your text field, if not return the original search phrase to your variables.

Check attached.

[#10]

Thanks for this hint.

I tried to make a version without the macro, but with a button that triggers selection in the text field, according to what the IF- statement in the list box gives. I.e :

Field:
Text
Search string:
='(' & concatenate(.., '|') & ')'

but there seems to be some problems with how the variable sets the triggers. I can't get it to work. The reaction in the filtered list works as one could expect, and that's a really good conclusion.

I am fully aware of the no-no about QlikView and macros, but in this case I think I'll stick to the macro solution. Unless, of course, someone can help me with how to push a select on the Text field, when user has input the search cirteria. In my case, filtering one list box is not enough. The filtering has to be done on the whole data set.

Since I'm not anymore discussing the problem that the head line states, I'll close this thread for now and ask for help somewhere else, if I cannot get the no-macro way working and if there is a demand of creating macro free solutions.

Thank you for your answers and help!

Sören