Alt function issue

[SGV]

All,

Is this a bug with Alt function? see below:

@hic @sunny_talwar 

[marcus_sommer]

IMO there is no bug - everything worked like expected. Left() is a string-functions and returned a string and therefore alt() behaved correctly. Isnum() returned true because the left-result could be treated as a number. That the pure left-expression results are displayed right oriented could be just a visualization/interpretation setting and it are further strings or the table-object did an auto-interpretation.

- Marcus  

[Or]

This one's actually an odd duck which might be a bug, or at least somewhat unexpected behavior, because it seems inconsistent:

If you hard-input the string '30s', it works correctly, but if you feed it as a field value, it doesn't. It should be noted that if you use e.g. Left(Field)+1, then Qlik has no problem interpreting that as a number and returning a correct result of 4. You also get a numeric interpretation using Left(Field,1) if the first value was '20s' instead of '>20'.

I also tried Alt(Left(FieldValue('Field',2),1),'Test') -this returns 3 rather than 'Test', so within that context Qlik is clearly re-interpreting the result of the function to see if it's numeric. However, Alt(Left(FieldValue('Field',RowNo()),1),'Test') returns 'Test' for both lines.

To me, this seems like a bug or an inconsistency in the way the functions are applied. It seems like Qlik might be evaluating ALL values of the function first, then determining the type, and then applying alt(), instead of doing it one line at a time.

[marcus_sommer]

Yes, you are right - the behaviour of Qlik isn't consistent. By pure field-values it behaved like described in the documentation but if the content is a manually string or a calculation-result it's interpreted. I assume Qlik will call it as working as designed but IMO there should be more context within the help.

If there is a scenario in which it's essential that a string-function result isn't unwanted interpreted it could be wrapped with text() which did prevent it in my current testing with alt() and rangesum().

- Marcus 

[Or]

Ultimately this can be worked around by forcing a numeric interpretation - Alt(num(Left(Field,1)),'Test') will behave "as expected" and return Test for >20, 3 for 30s. But it sure is an odd way to make alt() behave in my opinion - it should be applying num() on its own, that's literally what the function is supposed to do. Curious to see what Qlik will say about this one.

[hic · Accepted solution]

Field values in the Qlik engine are duals: {str,num}, i.e. they are values that have both a string representation and a numeric representation. Then you have two methods to test whether a value is a number:
1: Is there a value for "num" in the dual?
2: Can "str" be interpreted as a number?

The Left() function returns {str,-}. Alt() uses method 1, and IsNum() uses method 2, so they have different views on whether the parameter is a number. This is correctly described in the documentation, so - No - this is not a bug.

However, you could claim that it is inconsistent. I think the reason is that we have had different use cases for Alt() and IsNum(), and therefore implemented it in different ways.

HIC

[Or]

@hicBut how is the dual generated for the result of a function? Why does Alt(Left('30s',1),'str') return 3, but Alt(Left(Field,1),'str') returns 'str' with the example data I cited above? Put differently, why does the fact that a row exists with Field value of '>20' (but this value is not part of the row/data currently being evaluated) impact the result of an Alt() function on the Field value of '30s'? If I read your post correctly, I guess the answer is that the following steps are being taken in order?

1) The Qlik engine generates a result set of Left(Field,1)

2) The Qlik engine determines the type of this result set, which in the example cited above is going to be text (rather than a numeric dual pair) because at least one value is non-numeric

3) Because at least one value is not numeric, Alt() treats all values as text without attempting to interpret them as numeric values

But to me this does not match the Alt() documentation, which states that it tests for "valid number representation" - Left('30s',1) is 3, which has a valid number representation. That said, I'm not sure how to phrase it better...