Leading C in string makes QlikView interpret the value as an integer

[#1]

Dear all

I have a very weird issue I my document I can't solve. I am performing a load with five distinct IDs to map them to same values. But QlikView interprets those five IDs as only three distinct IDs. I figured out, that the problem is a leading C in the string. All strings leading with a C are interpreted as an integer value (e.g. CH123 is interpreted as 123, C123 as 123). The weird think is, that when I load a large flat table with 30000+ rows, this behaviour cannot be reproduced. Thus, in one load QlikView interprets CH123 as 123, in another load (but in the same document) QlikView interprets CH123 as CH123.

The main problem is, that due to this behaviour I cannot perform joins or mapping in my data when the ID start with an C (as I am working in Switzerland, CHxyz is a very common identifier for a business unit / legal entity).

Of course, there are work arounds like temporarily replacing all C's with another character to join data. But as I wish to understand why QlikView is behaving like this, I hope anyone can help me?

Example:

Load * Inline [

      FieldID, FieldValue

      CH123, CH123 Wert

      H123, H123 Wert

      123, 123 Wert

      C123, C123 Wert

      DE123, DE123 Wert

];

When creating a pivot table, you expect something like this:

FieldID	FieldValue
CH123	CH123 Wert
H123	H123 Wert
123	123 Wert
C123	C123 Wert
DE123	DE123 Wert

But I get the following pivot as a result.

You can see, that CH123 is aligned right while the other values are correctly aligned left. This results from the following setting:

Pivot properties -> Presentation tab -> Alignment -> Data (Numeric): Right

Pivot properties -> Presentation tab -> Alignment -> Data (Test): Left

[alexandros17]

I tried with both version of qlik I have and I cannot be able to reproduce the problem.

Try with a straight table or with a simple table and check the result

let me know

[tresesco]

I get otherwise.

PFA

QV-11.2 SR6

[#4]

Use the text function in the Load statement to ensure QlikView not interpret the value as a number.

[ali_hijazi]

yes this is it the Text function

[flipside · Accepted solution]

I can replicate this by setting MoneyFormat to a 'CHF' prefix, so maybe this is the cause - in some of your documents this may be set differently, perhaps. The fix, as others have stated, is to force this data into a string using the Text function.

flipside

[flipside]

Just to add, you might be able to continue using the CHF currency prefix by preceding the code with an empty space in the MoneyFormat declaration. When text string data with preceding spaces are loaded, it looks like the spaces are stripped away and therefore doesn't match the currency prefix.

[#8]

Thankyou flipside, the MoneyFormat was the issue I was looking for. The text() function is also helpful.