Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
I get otherwise.
PFA
QV-11.2 SR6
Use the text function in the Load statement to ensure QlikView not interpret the value as a number.
yes this is it the Text function
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
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.
Thankyou flipside, the MoneyFormat was the issue I was looking for. The text() function is also helpful.