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

[marcus_sommer]

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('True', true()),

                              dual('False', false()),

                              dual($1, $1)),

                         'Null');

- Marcus

[Anonymous]

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

[Anonymous]

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

[marcus_sommer]

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

[Anonymous]

  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('True', true()),

                              dual('False', false()),

                              dual($1, $1)),

                         'Null');

  Regards,

  Goran

[swuehl]

Try

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

[Anonymous]

  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.

[marcus_sommer]

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

[Miguel_Angel_Baeyens]

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