Discussion Board for collaboration on QlikView Scripting.
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
Have a look at this blog post: On Format Codes for Numbers and Dates. The R is interpreted as indicating a radix number format.
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.
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...
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...
No I have not. It was just to test various transformations on the normal InstrumentCode field.
''''& InstrumentCode & '''' as InstrumentCodeWithQuotes,
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.