topic Boolean values interpretation/presentation inconsistency in QlikView

Boolean values interpretation/presentation inconsistency

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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:

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,

False, True

True, False

True, True

False,

True,

, False

, True

] (delimiter is ',');

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

Re: Boolean values interpretation/presentation inconsistency

marcus_sommer — Sat, 06 May 2017 11:26:58 GMT

I'm not quite sure what do you want to do but maybe the following approach is helpful in your case:

Set fBoolean# = alt(pick(wildmatch($1, 0, -1, 'True', 'False', '*'),

dual('False', false()),

dual('True', true()),

dual('False', false()),

dual($1, $1)),

'Null');

- Marcus

Re: Boolean values interpretation/presentation inconsistency

Anonymous — Sat, 06 May 2017 17:35:07 GMT

The idea was to try not to treat 0 and -1 as Boolean False and True respectively but simply as 0 and -1, instead, what I got was an inconsistent presentation, 0 was treated by QlikView as Boolean False and presented as 'False', while -1 was not presented as 'True' but as -1 although it's treated as Boolean True. Here is the slightly modified fBoolean# variable which yields exactly the same result as before:

Script:

Set NullInterpret = '';

Set fBoolean# = If($1 = 0 or $1 = -1, $1, If($1 = 'True', Dual('True', True()), If($1 = 'False', Dual('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 ',');

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

Re: Boolean values interpretation/presentation inconsistency

Anonymous — Sat, 06 May 2017 17:50:50 GMT

Here is another example which proves my point, the code is exactly the same, the only thing different is that I moved the first record (False, False) down below, QlikView/Qlik Sense now changes the presentation of Boolean values False and True depending on the order of records, when "False, False" is the first record in the previous example then 0 is going to be treated as Boolean False and shown as 'False', -1 is going to be treated as Boolean True but shown as -1; when the first record contains numerical values "-1, 0" then Boolean values False and True are going to be shown as 0 and -1 respectively; the treatment of 0 and -1 by QlikView/Qlik Sense is not deterministic/consistent without having to use a fairly complex transformation from your example just to be able to deal with Boolean values in a consistent manner and to be sure what to expect in the end:

Script:

Set NullInterpret = '';

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

Boolean:
Load Xc, Yc, $(fBoolean#(Xc)) as X, $(fBoolean#(Yc)) as Y
Inline [
Xc, Yc
-1, 0
0, 0
0, -1
-1, 0
-1, -1
, 0
, -1
-1,
0,
False, False
False, True
True, False
True, True
False,
True,
, False
, True
] (delimiter is ',');

Output:

Xc,Yc,X,Y

-1,0,-1,0

0,0,0,0

0,-1,0,-1

-1,0,-1,0

-1,-1,-1,-1

,0,,0

,-1,,-1

-1,,-1,

0,,0,

False,False,0,0

False,True,0,-1

True,False,-1,0

True,True,-1,-1

False,,0,

True,,-1,

,False,,0

,True,,-1

Re: Boolean values interpretation/presentation inconsistency

marcus_sommer — Sun, 07 May 2017 07:44:22 GMT

I think this posting: Data Types in QlikView and the both links on the end of them will be explain your observation. It's not a bug - it's designed in this way and if you need a certain representation you could force them by the consequent use of formatting-functions and/or the dual-function. Here an example of both load-orders:

and their checking within excel.

- Marcus

Re: Boolean values interpretation/presentation inconsistency

Anonymous — Mon, 08 May 2017 18:52:00 GMT

Marcus, thanks for your insight into Boolean type representation in QlikView/Qlik Sense.To me, based on what I've observed this appears as a design flaw rather than a design feature, I don't know of any other product that behaves like this, that the results of an expression would be dependent on the contents of the first record in a table. Your approach seems to be the only way to ensure a consistent Boolean value interpretation/presentation:

Set fBoolean# = alt(pick(wildmatch($1, 0, -1, 'True', 'False', '*'),

dual('False', false()),

dual('True', true()),

dual('False', false()),

dual($1, $1)),

'Null');

Regards,

Goran

Re: Boolean values interpretation/presentation inconsistency

swuehl — Mon, 08 May 2017 20:10:29 GMT

Try

Set fBoolean# = Num(If($1 = false(), false(), if($1=true(), true())));

Re: Boolean values interpretation/presentation inconsistency

Anonymous — Sun, 24 Dec 2017 02:32:38 GMT

Here is another much simpler example of the behavior I've observed:

// <Start of script>

Set NullInterpret = '';

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

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

Set NullInterpret =;

// <End of script>

This is the result I get, the last 2 records indicate that concatenation of a Null with 1 and 1 with a Null gives "01" in field [X & Y] as a consequence of a prior concatenation of 0 with 1 (first record). It also appears that in order to have a Null properly concatenated with a numeric, I'll always have to use the Text() function to wrap the X & Y expression, Text(X & Y):

X,Y,X & Y,Text(X & Y)

0,1,01,01

1,0,10,10

,1,01,1

1,,01,1

I am wondering if there is a way for string concatenation (&) to work without having to use Text() function, as in Text(X & Y), in order to have numbers properly concatenated, in this example, a Null gets interpreted as 0 in either X or Y fields in the last two records. In general, one might have a mix of numeric and alphanumeric values in both X and Y fields, Qlik Sense is not necessarily going to concatenate numbers properly, that will depend on the (numeric) values that appeared in the previous records, thus output from X & Y is not deterministic, it's rather speculative.

Re: Boolean values interpretation/presentation inconsistency

marcus_sommer — Wed, 27 Dec 2017 10:26:19 GMT

I could confirm your observed behaviour of qlik data-interpretation is correct and that the load-order made a difference how the data of a column are interpreted and that the first record determined the format. Whereby it's particularly interesting that an existing NULL value could become 0 if fields are string-concatenated.

I'm not sure if this behaviour is a pitfall of the intentionally simplified qlik-design of using no real data-types or if it should be more considered as a bug.

In the end it meant that you might need to adjust / to transform the data-input to your expected data-output like in many other tools or languages where you must define in beforehand which data-type and length a field/variable has which often leads to a lot of wasted resources and to mismatch-errors if the assumption of the data-input is wrong or if there is a poor data-quality. From this point of view I appreciate the qlik-logic more as a benefit than as a disadvantage.

To check if there are alternatives I have extended your example to a few more ways of interpreting the input and concatenating the fields and unless your already used text(X & Y) - which is the common suggestion for reading fields with particular demands - there seems nothing of the ordinary measures which seems helpful (including also various NULL settings). This was of course not very satisfactory and went on and I discovered that it only touched the format / display within the UI and not the data itself by checking the field-content with len() and ord().

This meant by using this concatenated field only within the script no adjustments seems necessary and for displaying them within the UI a measure like text() or also a user-defined function (I have included one) should work.

set SetNull = if(len(trim($1))=0,chr(8203),$1);

AutomaticFormat:
Load
*, rowno() as RowNo, X & Y as [X & Y], Text(X & Y) as [Text(X & Y)], Text(X) & text(Y) as [Text(X) & text(Y)],
num(X) & num(Y) as [num(X) & num(Y)], num#(X) & num#(Y) as [num#(X) & num#(Y)],
dual(X, X) & dual(Y, Y) as [dual(X, X) & dual(Y, Y)],
alt(X, '') & alt(Y, '') as [alt(X, '') & alt(Y, '')],
$(SetNull(X)) & $(SetNull(Y)) as SetNull,
trim(X) & trim(Y) as [trim X & Y],

len(X & Y) as [len(X & Y)],
ord(mid(X & Y, 1, 1)) as [ord(mid(X & Y, 1, 1))], ord(mid(X & Y, 2, 1)) as [ord(mid(X & Y, 2, 1))],
X & Y + 10 as [X & Y + 10];
Load X, Y
Inline [
X,Y
0,1
1,0
,1
1,
] (txt, delimiter is ',', msq, embedded labels);

left join(AutomaticFormat)
load X, Y, concat(X & Y) as [concat(X & Y)] resident AutomaticFormat group by X, Y;

- Marcus

Re: Boolean values interpretation/presentation inconsistency

Miguel_Angel_Baeyens — Wed, 27 Dec 2017 14:33:19 GMT

For nulls, short answer is that INLINE works the same way than a CSV or XLS file: there can be empty values, or blank values if no value is present, but they cannot be "true null".

For first line defining the interpretation of following values in same column, short answer is that has been like that as far as I can remember, and actually used it for development years ago, bug or feature.

In your example for the concatenation, you are using a preceding load and NullInterpret is applied on it, but if you use instead

// <Start of script>

Set NullInterpret = '';

NullPropagation:
Load *, X & Y as [X & Y], Text(X & Y) as [Text(X & Y)]
Inline [

...

You will get the nulls so both [X & Y] and [Text(X & Y)] will display "1" instead of "01".

I use this approach of "first-record-defines" in most cases when I don't know how the driver to the data source is configured, e.g.: some drivers remove some non-ASCII characters like blanks which could impact the expected output, similar to what the Verbatim script variable does.

Finally, note that as mentioned by marcus_sommer‌, there are no data types in QlikView, so any value will be both literal and numeric (unless a numeric value is not possible for the string, e.g.: Num(String) if "String" is not a variable or a field with only one possible value given the selection status) and therefore can be represented in either way.

Yes, this is different to how databases work, because in fact, QlikView is not a database (although it has its own storage format with QVD files).

EDIT: "first line definition" can be found in the official help site at Data types in QlikView ‒ QlikView

Re: Boolean values interpretation/presentation inconsistency

Anonymous — Wed, 27 Dec 2017 17:58:12 GMT

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,

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

Re: Boolean values interpretation/presentation inconsistency

Anonymous — Sat, 06 Jan 2018 18:13:59 GMT

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.

Re: Boolean values interpretation/presentation inconsistency

Anonymous — Sat, 06 Jan 2018 18:15:05 GMT

Great idea, to use a stub.

Re: Boolean values interpretation/presentation inconsistency

Anonymous — Sat, 06 Jan 2018 22:01:12 GMT

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

Re: Boolean values interpretation/presentation inconsistency

petter — Sun, 07 Jan 2018 03:24:36 GMT

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

Re: Boolean values interpretation/presentation inconsistency

Peter_Cammaert — Sun, 07 Jan 2018 19:17:37 GMT

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.

Re: Boolean values interpretation/presentation inconsistency

Miguel_Angel_Baeyens — Mon, 08 Jan 2018 17:23:14 GMT

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

Re: Boolean values interpretation/presentation inconsistency

Anonymous — Tue, 09 Jan 2018 03:16:13 GMT

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

Re: Boolean values interpretation/presentation inconsistency

swuehl — Tue, 09 Jan 2018 11:27:44 GMT

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