Concatenation with Null issue

[Anonymous]

I ran the following script in Qlik Sense Desktop 3.1 SR3:

<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, 0
0, 1
1, 0
1, 1
, 0
, 1
1,
0,
] (delimiter is ',');

<End of script>

  This is the result I get, the values in field [X & Y] appear to be invalid in the last 4 records. It appears that the formats of the values in the first record defines the formats that are going to be used for the rest of the records. 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 X & Y, Text(X & Y); one would probably have to use the Text function to wrap concatenation expressions which involve either numeric and/or alphanumeric types as well as Nulls every time in order to ensure correct and consistent results:

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

0,0,00,00

0,1,01,01

1,0,10,10

1,1,11,11

,0,00,0

,1,01,1

1,,01,1

0,,00,0

[swuehl]

I think there are two things to note here:

a) QlikView can store values as duals, i.e. a value consist of a numeric representation and a string representation, for example any Qlik dates are in fact numbers, but formatted to date representation like 'MM/DD/YYYY'.

Data Types in QlikView

It's important to note that any distinct number (field value) can only show a single format, the same field value can not have two distinct formats defined in the symbol table.

b) If Qlik encounters a text string, it often tries to automatically interpret it as number, and if it does so when loading in the data, it assigns the interpreted number as numeric representation of the field value and the original text value as text representation, but it can only do the assignment for the first appearance of the value, Qlik will override any occurence of the same number with different format with the first format loaded (or in other words: there is only one place for a single value in the symbol table, the text representation is determined by the first representation in load order).

Automatic Number Interpretation

I believe this already explains what you see, 2nd, 6th and 7th line are all interpreted as 1, with format 01

The automatic number interpretation kicks in for the result of your expression X&Y but not if you use Text(X&Y)