QlikView changing text value

[Anonymous]

Good day,

I have an interesting scenario where I read a database view containing financial instruments.  I noticed that when I query the view I get two records based on the INSTRUMENT_CODE but in Qlikview I see 4 records.

On closer inspection and after I changed the script to convert the INSTUMENT_CODE to text,

i.e. "TEXT(InstrumentCode) as InstrumenCodeText"

I noticed that the actual text values differ from what Qlikview displays in the untransformed value ...

Is this a QlikView defect ?  It's a bit concerning from a development point of view.

The application was developed in QlikView Desktop V12.20.20100.0

InstrumenCodeText	Id	InstrumentCode
R207	21606	R207
207	41978	R207
R 207	80869	R207
R207	158775	R207

Kind regards

Edwin

[hic]

There is no automatic number interpretation of the R36 format, so 'R207' should normally be a string and nothing else. This can be tested by the following script:

Load
InstrumentCode,
Text(InstrumentCode),
Num(InstrumentCode)
Inline
[InstrumentCode
R207];

which in my QV 12.20 returns the fields as you would expect: Left-aligned strings and no numeric interpretation:

See also Automatic Number Interpretation

However, there is a possible explanation: I see that you live in South Africa, and so you might have a Money format looking like

SET MoneyFormat='R#,##0.00;-R#,##0.00';
 

which could trigger a money interpretation, and then you get the format of the first instance for all four records.

HIC

[Anonymous]

Hi Henric,

Yes it could be that, just a note, the source data has three different values, QlikView interprets the following three loaded values the same:

'207'

'R207'

'R 207'

Therefore the concern is that all the values becomes 'R207' even the '207' value...

But I assume it might translate it to a currency column.

Also interesting, in my application when I clear my selection to see fi any of the other codes has a similar value I get an error: "Internal Inconsistency, type A"

Kind regards

Edwin

[Anonymous]

So yeh it seems as if there's no real good solution to this.  If you transform it to text, leading zero's are dropped:
0003456 becomes 3456, if you transform it to num it's a similar behaviour.

R 207 becomes R 207 as text but the original value is drops the R and just becomes 207 ... ???

The field is not a fixed length....

Here are some interesting samples:

InstrumenCodeText	InstrumentCode	=Replace(InstrumentCodeWithQuotes, '"', '')	InstrumenCodeNum	if(InstrumenCodeText <> InstrumentCode, 1, null())
				-
0000	0	0		1
00000	0	0		1
0.00	0	0		1
4.00	4	4	4	1
3128	3128	3128	3128	1
12508E101	1.2508E+105	1.2508E+105	1250800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000	1
28262	28262	28262	28262	1
35250	35250	35250	35250	1
45336E109	4.5336E+113	4.5336E+113	##################################################################################	1
45614	45614	45614	45614	1
47245	47245	47245	47245	1
100159	100159	100159	100159	1
135087E26	1.35087E+31	1.35087E+31	13508700000000000000000000000000	1
135087E42	1.35087E+47	1.35087E+47	135087000000000000000000000000000000000000000000	1
135087E59	1.35087E+64	1.35087E+64	13508700000000000000000000000000000000000000000000000000000000000	1
135087E67	1.35087E+72	1.35087E+72	1350870000000000000000000000000000000000000000000000000000000000000000000	1
136701	136701	136701	136701	1
141192	141192	141192	141192	1
150080	150080	150080	150080	1
182704	182704	182704	182704	1
185929	185929	185929	185929	1
216238	216238	216238	216238	1
233527	233527	233527	233527	1
237400	237400	237400	237400	1
239581	239581	239581	239581	1
240549	240549	240549	240549	1
242493	242493	242493	242493	1
263494	263494	263494	263494	1
265274	265274	265274	265274	1
286941	286941	286941	286941	1
287580	287580	287580	287580	1
309644	309644	309644	309644	1
330860	330860	330860	330860	1
338530	338530	338530	338530	1
345217	345217	345217	345217	1
406501	406501	406501	406501	1
408284	408284	408284	408284	1
414850	414850	414850	414850	1
416102	416102	416102	416102	1
454492	454492	454492	454492	1
456443	456443	456443	456443	1
457963	457963	457963	457963	1
1055102	1055102	1055102	1055102	1
1084102	1084102	1084102	1084102	1
2824100	2824100	2824100	2824100	1
12653101	12653101	12653101	12653101	1
13817101	13817101	13817101	13817101	1
15271109	15271109	15271109	15271109	1
15351109	15351109	15351109	15351109	1
16255101	16255101	16255101	16255101	1
17175100	17175100	17175100	17175100	1
18581108	18581108	18581108	18581108	1
18802108	18802108	18802108	18802108	1
20002101	20002101	20002101	20002101	1
23135106	23135106	23135106	23135106	1
23586100	23586100	23586100	23586100	1
23608102	23608102	23608102	23608102	1
25537101	25537101	25537101	25537101	1
25816109	25816109	25816109	25816109	1
25932104	25932104	25932104	25932104	1
26874784	26874784	26874784	26874784	1
30420103	30420103	30420103	30420103	1
31100100	31100100	31100100	31100100	1
31162100	31162100	31162100	31162100	1
32095101	32095101	32095101	32095101	1
32511107	32511107	32511107	32511107	1
32654105	32654105	32654105	32654105	1
35710409	35710409	35710409	35710409	1
36752103	36752103	36752103	36752103	1
37411105	37411105	37411105	37411105	1
37833100	37833100	37833100	37833100	1
38222105	38222105	38222105	38222105	1
39483102	39483102	39483102	39483102	1
40413106	40413106	40413106	40413106	1
42735100	42735100	42735100	42735100	1
R 207	207	207	207	1
R207	207	207	207	1

[hic]

Most likely it will work properly if you change your money format to

SET MoneyFormat='#,##0.00;-#,##0.00';

HIC