Boolean values interpretation/presentation inconsistency

[Anonymous]

  I encountered the following Boolean values interpretation/presentation inconsistency, Boolean False/0 is interpreted/presented as False, while True/-1 is interpreted as -1. I also found out that Boolean value interpretation/presentation varies depending on the order of the input records, the result is thus nondeterministic. I am wondering if there is a way to make it consistent/deterministic so that I don't get different results for exactly the same data depending on the way records were ordered:

<Start of script:>

Set fBoolean# = If($1 = 0 or $1 = -1, $1, If($1 = 'True', True(), If($1 = 'False', False(), $1)));

Boolean:

Load Xc, Yc, $(fBoolean#(Xc)) as X, $(fBoolean#(Yc)) as Y

Inline [

Xc, Yc

False, False

-1, 0

0, 0

0, -1

-1, 0

-1, -1

, 0

, -1

-1,

0,

False, True

True, False

True, True

False,

True,

, False

, True

] (delimiter is ',');

<End of script>

Output:

Xc,Yc,X,Y

False,False,False,False

-1,0,-1,False

0,0,False,False

0,-1,False,-1

-1,0,-1,False

-1,-1,-1,-1

,0,,False

,-1,,-1

-1,,-1,

0,,False,

False,True,False,-1

True,False,-1,False

True,True,-1,-1

False,,False,

True,,-1,

,False,,False

,True,,-1

[Anonymous]

If one first creates a stub table with zero rows you can force columns X & Y to be Boolean :

Boolean :

Load

    True()  as X ,

    True()  as Y

autogenerate 0

;

Then one can concatenate the data onto this zero row stub and columns X & Y will remain Boolean as they have been pre-forced :

Set fBoolean# = If($1 = 0 or $1 = -1

                              , $1

                              , If($1 = 'True'

                                   , True()

                                  , If($1 = 'False'

                                       , False()

                                 , $1 ) ) ) ;

Concatenate (Boolean)

Load

     Xc,

    Yc,

     $(fBoolean#(Xc)) as X,

    $(fBoolean#(Yc)) as Y

Inline [

Xc, Yc

False, False

-1, 0

0, 0

0, -1

-1, 0

-1, -1

, 0

, -1

-1,

0,

False, True

True, False

True, True

False,

True,

, False

, True

];

Then one should find that all the values for X and Y are Boolean -1 or 0, with the exception of those with nothing defined for them in the Inline Load for Xc and Yc which are interpreted as zero length strings, which are not trapped in the fBoolean# function.

If one wanted to make them False() , then changing the fBoolean# function to this should suffice :

Set fBoolean# = If ( len($1) = 0

                              , False()

                              , If($1 = 0 or $1 = -1

                                   , $1

                                   , If($1 = 'True'

                                        , True()

                                       , If($1 = 'False'

                                            , False()

                                           , $1 ) ) ) ) ;

[Anonymous]

Even without the preceding load I used originally, I get exactly the same behavior I used to get in the past in the most recent Qlik Sense version, X & Y will still display "01" and Text(X & Y) will still display "1" when X is an empty string, i.e. null and Y = 1. As you've already mentioned, the first string representation/format encountered for X = 0 & Y = 1, which is "01", will be used for any X & Y which results in a numerical value of 1 as per: https://help.qlik.com/en-US/sense/November2017/Subsystems/Hub/Content/Scripting/data-types.htm, which is the default behavior I am having a problem with since it may lead to confusion, it's representation versus actual value that's stored and they are not necessarily aligned. To make concatenation of numbers or even alphanumerical strings work in general as I would expect it to work is to use the Text() function as in Text(X & Y) instead of X & Y, for instance, this would be the way to preserve original leading zeros if they are significant, like in ID alphanumerical fields where every character is significant.

[Anonymous]

Great idea, to use a stub.

[Anonymous]

Marcus, in the end, it appears that the only safe way of concatenating alphanumeric fields in general is to always wrap the concatenation expression with the Text() function as in Text(X & Y). This will make Qlik Sense use the number representations of both X and Y (actual numeric values stored in these two fields) and not use the string representation of the numbers being concatenated instead, that eliminates this anomaly caused by Qlik's default string representation/formatting of numbers, I don't see a reason for having this feature in Qlik Sense which should be based on a sound mathematical foundation instead where this feature will have no place (https://help.qlik.com/en-US/sense/November2017/Subsystems/Hub/Content/Scripting/data-types.htm).

P.S.

After I added a resident load step and slightly changed one input value for X, I tried to concatenate X and Y there as well which resulted in an unexpected result:

// <Start of script>

Set NullInterpret = '';

NullPropagation1:
Load *,
     Text([X & Y]) as "Text([X & Y])";
Load *,
     X & Y as [X & Y], Text(X & Y) as [Text(X & Y)];
Load X, Y
Inline [
X, Y
0, 1
01, 0
, 1
1,
] (delimiter is ',');

NullPropagation:
Load *,
     Text(X & Y) as "Text(X & Y)-resident",
     Text([X & Y]) as "Text([X & Y])-resident" Resident NullPropagation1;

Drop Table NullPropagation1;

Store NullPropagation into [lib://Data/NullPropagation.csv] (txt);
Store NullPropagation into [lib://Data/NullPropagation.qvd] (qvd);

Set NullInterpret =;

Exit Script;

// <End of script>

This is what I got, it turns out that concatenation during resident load produces wrong result, field "Text(X & Y)-resident" has "01" instead of "1" in the last record due to the fact that what's loaded/stored in fields X and Y in the final table NullPropagation is not the original values from resident table NullPropagation1, but their formatted values, which is something I'll have to keep in mind and investigate further. What I'll do, I'll export both tables into qvd files in order to see what exactly was stored in fields X and Y in each of them, whether the original number or it was string representation/formatted value that was stored instead:

X,Y,X & Y,Text(X & Y),Text([X & Y]),Text(X & Y)-resident,Text([X & Y])-resident

0,1,01,01,01,01,01

01,0,010,010,010,010,010

,1,01,1,1,1,01

01,,01,1,1,01,01

[petter]

If you use this variable-function definition you will get consistent Qlik booleans:

Set fBoolean# = If($1=0,False(),If($1=-1,True(), If($1 = 'True', True(), If($1 = 'False', False(), $1))));

[Peter_Cammaert]

As far as I know, the QV load script language (I guess QS is still quite similar) has no "Boolean" concept. There are only these certainties:

• True().is a function returning -1
• False() is a function returning 0
• Non-zero means success
• Zero means failure
• Formatting is up to you

You could call 3 & 4 true and false, but that's paint on top of behavior that strongly resembles early languages like C where every non-zero value meant true but "true" didn't really exist.

There is a another link to "early languages". Remember that the Load Script Language is not a managed code OOP language and doesn't protect programmers against themselves in any way, as most modern languages tend to do. IMHO this is -as Marcus already hinted at - because the language serves only one emperor: speed. I tend to believe that all anomalies that have been discovered by the community through the years, all so-called "design flaws", all inconsistencies are there simply because of design decisions and the results thereof that serve the god of speed.

Just to say that there are probably a few pragmatic reasons why this behavior is what it is. We just need to figure out what they are.

[Miguel_Angel_Baeyens]

I can upload a QlikView version (I cannot install Qlik Sense in this computer) in the coming days to show how it works and that null is null (not displayed or converted into 0). I already made the example but I have to find it, if that is useful.

Use the LOAD INLINE if you want to explicit the format you expect, but bear in mind that QlikView does not have "boolean" types as it does not have any other type: all values can be represented as a string (e.g.: 00001, 08/01/2018) numeric (e.g.: 1, 0.00396 -which equals to 8 divided by 1 divided by 2018), or both. False is 0 and True is -1 (actually, true is everything that is not 0, e.g.: If(Len('Text'), 1, 0), you needn't specify ">0").

[Anonymous]

I agree, I view this particular design decision regarding the string representation/formatting of numbers as per https://help.qlik.com/en-US/sense/November2017/Subsystems/Hub/Content/Scripting/data-types.htm, as an arbitrary decision, not based on a solid mathematical theory. I don't believe that the speed of execution was a factor in this particular case as there is an additional cost associated with figuring out which particular string representation/format should be assigned to each distinct numerical value anyways, it was just plain wrong to do it this particular way. That has lead to anomalies described above as well as the one you'll find in this example, please note the transition from the original numeric value of field X in the first table to the string representation/formatted value of X in the second table in the Text(X) calculation:

// <Start of script>

Set NullInterpret = '';

NumberStringRepresentation:
Load 'NumberStringRepresentation1' as Source, X, Text(X) as [Text(X)]
Inline [
X, Y
1.0
1
1.000
] (delimiter is ',', msq, embedded labels);

Concatenate(NumberStringRepresentation)
Load 'NumberStringRepresentation2' as Source, X, Text(X) as [Text(X)]
Resident NumberStringRepresentation;

Store NumberStringRepresentation into [lib://Data/NumberStringRepresentation.csv] (txt);
Store NumberStringRepresentation into [lib://Data/NumberStringRepresentation.qvd] (qvd);

Set NullInterpret =;

// <End of script>

This is the result:

Source,X,Text(X)

NumberStringRepresentation1,1.0,1.0

NumberStringRepresentation1,1.0,1

NumberStringRepresentation1,1.0,1.000

NumberStringRepresentation2,1.0,1.0

NumberStringRepresentation2,1.0,1.0

NumberStringRepresentation2,1.0,1.0

[swuehl]

Maybe have a look at

Symbol Tables and Bit-Stuffed Pointers

QlikView is not able to store distinct formats of the same number per data table record, but only one format per symbol.

Your numbers 1, 1.0, 1.000 will be seen as one symbol, text value '1.0' (because of the first encounter) and numerical value one (please refer to the data type blog by HIC to learn about about duals).

[edit: This is referring to your X column. Your Text(x) column creates 3 different symbols, one per input record in your first load.]

While your first LOAD is accessing your original input records, the second is only able to map the symbols, that's why you get the same text representation for all records in your second load.

Hope this makes it somewhat clearer.

Stefan