Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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.
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...
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
I hope you have not used the quoted text to convert the values to text..
Text(InstrumentCodeWithQuotes) as InstrumentCodeText
?
No I have not. It was just to test various transformations on the normal InstrumentCode field.
''''& InstrumentCode & '''' as InstrumentCodeWithQuotes,
Kind regards
Edwin
Could you provide some sample data ?
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.