topic Re: Using special characters in Calculated Dimension in QLikView in QlikView

Using special characters in Calculated Dimension in QLikView

Mon, 07 Jul 2014 08:42:56 GMT

I have a Calculated Dimension in a Pivot Table with some characters which may be interpreted as special characters. I suspect that this is the cause for the 'error in calculated dimension' which i get.

Characters include '/', '.', '&', '(', ')'. Which characters are indeed special characters, and how to make them been considered as normal characters ?

Thanks in advance

Re: Using special characters in Calculated Dimension in QLikView

alexandros17 — Mon, 07 Jul 2014 08:44:03 GMT

Use chr(nnn) for special carachers

Re: Using special characters in Calculated Dimension in QLikView

Mon, 07 Jul 2014 08:44:32 GMT

thanks. that is to say chr(\), chr(&), etc... ?

Re: Using special characters in Calculated Dimension in QLikView

Mon, 07 Jul 2014 09:17:36 GMT

Additionally, there is a quote character ' inside a string. How to make it considered as a character in the string ?

thanks in advance

Re: Using special characters in Calculated Dimension in QLikView

its_anandrjs — Mon, 07 Jul 2014 09:22:27 GMT

Use CHR() function in the calculated dimension

Ex:-

Chr(92) and Chr(38) in the calculated expression

Re: Re: Using special characters in Calculated Dimension in QLikView

shane_spencer — Mon, 07 Jul 2014 09:24:41 GMT

Have a look at this example using CHR(39) instead of '

Re: Using special characters in Calculated Dimension in QLikView

Mon, 07 Jul 2014 09:25:19 GMT

do i simply replace in the string the character by chr(xx) or what is the syntax ?

Thanks for your help

Re: Using special characters in Calculated Dimension in QLikView

shane_spencer — Mon, 07 Jul 2014 09:28:24 GMT

Replace the special character such as ' with the corresponding CHR() i.e. CHR(39) is equal to '

Re: Using special characters in Calculated Dimension in QLikView

its_anandrjs — Mon, 07 Jul 2014 09:33:46 GMT

Use Replace function with the Chr() function and use that Chr value to replace

Re: Using special characters in Calculated Dimension in QLikView

Mon, 07 Jul 2014 09:37:59 GMT

I cannot open the file in the Personal Edition. Would you accept to reproduce the expression here ?

Thanks

Re: Using special characters in Calculated Dimension in QLikView

shane_spencer — Mon, 07 Jul 2014 09:39:53 GMT

Paste the below in a QVW and look at the two tables.

Test:

LOAD * INLINE [

"1'2'3'4'5"

];

Load

SubField(F1, Chr(39), 1) as 1,

SubField(F1, Chr(39), 2) as 2,

SubField(F1, Chr(39), 3) as 3,

SubField(F1, Chr(39), 4) as 4,

SubField(F1, Chr(39), 5) as 5

resident Test;

Re: Using special characters in Calculated Dimension in QLikView

its_anandrjs — Mon, 07 Jul 2014 09:54:51 GMT

Another way is you can use Purgechar, keepchar or sub field functions to achieve this.

Ex:-

LOAD Concat(NewText1,'') as NewText2;

LOAD Text,KeepChar(Text,'abcdefghijklmnopqrstuvwxyz') as NewText,

SubField(SubField(SubField(SubField(SubField(Text,'/'),'.'),'&'),'('),')') as NewText1;

LOAD * Inline

[

Text

asdfjsda/dsda.dasdas&sdasd(sdas)

];

Re: Using special characters in Calculated Dimension in QLikView

Tue, 08 Jul 2014 16:17:01 GMT

Thanks for that all.

However, I am looking at a way to have special characters in a calculated dimension... and there if I replace single quote with chr(39), it writes the string Chr(39)...

Re: Using special characters in Calculated Dimension in QLikView

shane_spencer — Wed, 09 Jul 2014 07:25:13 GMT

Can you share your application with a little bit of sample data in it? That might help.