topic Re: QlikView changing text value in QlikView

QlikView changing text value

Anonymous — Wed, 09 May 2018 08:45:25 GMT

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

Re: QlikView changing text value

sunny_talwar — Wed, 09 May 2018 09:04:31 GMT

I think the others one can still be resolved, but the only one I am concerned about is row 2 which is missing an R. Is this truly the case or is this a typo?

Re: QlikView changing text value

Gysbert_Wassenaar — Wed, 09 May 2018 09:05:33 GMT

Have a look at this blog post: On Format Codes for Numbers and Dates‌. The R is interpreted as indicating a radix number format.

Re: QlikView changing text value

tresB — Wed, 09 May 2018 09:06:11 GMT

And what is the outcome you get if you try with num(), like:

"NUM(InstrumentCode) as InstrumenCodeNum"

I am just thinking a possibility if this is actually a dual field.

Re: QlikView changing text value

Anonymous — Wed, 09 May 2018 09:24:43 GMT

Hello Tresesco,

No it's not a typo... that's the way the data is:

InstrumenCodeText InstrumentCodeWithQuotes InstrumenCodeNum Id InstrumentCode

R207 'R207' 207 21606 R207

207 '207' 207 41978 R207

R 207 'R 207' 207 80869R R207

R207 'R207' 207 158775 R207

Here's what I get with the num conversion...

Re: QlikView changing text value

Anonymous — Wed, 09 May 2018 09:29:30 GMT

Hi Gysbert,

Thanks it's an interesting article. I am a bit concerned where the R is added to the value which is just 207... It just seems odd that QlikView would change the value like that. In essence it means that one would have to force all text to text fields in order to avoid this scenarios as you would not always pick it up and as in this scenario one would get 4 records instead of 2 which could have severe consequences if you for example to aggregations, etc...

Kind regards

Edwin

Re: QlikView changing text value

sasiparupudi1 — Wed, 09 May 2018 09:59:06 GMT

I hope you have not used the quoted text to convert the values to text..

Text(InstrumentCodeWithQuotes) as InstrumentCodeText

Re: QlikView changing text value

Anonymous — Wed, 09 May 2018 10:07:46 GMT

No I have not. It was just to test various transformations on the normal InstrumentCode field.

''''& InstrumentCode & '''' as InstrumentCodeWithQuotes,

Kind regards

Edwin

Re: QlikView changing text value

sasiparupudi1 — Wed, 09 May 2018 10:12:10 GMT

Could you provide some sample data ?

Re: QlikView changing text value

Anonymous — Wed, 09 May 2018 10:26:16 GMT

I will attached a sample app. It's a bit tricky to simulate with an INLINE load statement, however here you can also see that the value '207' becomes R207.... !? My data source is a MSSQL Database.

Re: QlikView changing text value

hic — Wed, 09 May 2018 11:51:04 GMT

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:

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

Re: QlikView changing text value

Anonymous — Wed, 09 May 2018 12:15:24 GMT

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

Re: QlikView changing text value

Anonymous — Thu, 10 May 2018 15:02:21 GMT

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

Re: QlikView changing text value

hic — Thu, 10 May 2018 16:52:08 GMT

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

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

HIC