Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Partner
Partner

QlikView changing text value

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

InstrumenCodeTextIdInstrumentCode
R20721606R207
20741978R207
R 20780869R207
R207158775R207

Kind regards

Edwin

Tags (1)
13 Replies
Highlighted

Re: QlikView changing text value

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?

Highlighted
MVP & Luminary
MVP & Luminary

Re: QlikView changing text value

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


talk is cheap, supply exceeds demand
Highlighted
MVP
MVP

Re: QlikView changing text value

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.

Highlighted
Partner
Partner

Re: QlikView changing text value

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

Highlighted
Partner
Partner

Re: QlikView changing text value

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

Highlighted
Honored Contributor III

Re: QlikView changing text value

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

Text(InstrumentCodeWithQuotes) as InstrumentCodeText

?

Highlighted
Partner
Partner

Re: QlikView changing text value

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

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

Kind regards

Edwin

Highlighted
Honored Contributor III

Re: QlikView changing text value

Could you provide some sample data ?

Highlighted
Partner
Partner

Re: QlikView changing text value

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.