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

[sunny_talwar]

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?

[Gysbert_Wassenaar]

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

[tresesco]

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.

[Anonymous]

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...

[Anonymous]

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

[sasiparupudi1]

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

Text(InstrumentCodeWithQuotes) as InstrumentCodeText

?

[Anonymous]

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

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

Kind regards

Edwin

[sasiparupudi1]

Could you provide some sample data ?

[Anonymous]

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.