topic Re: Set Analysis with ' sign in QlikView

Set Analysis with ' sign

Thu, 22 May 2014 13:49:39 GMT

Hi all,

Im trying to write a set analysis with a string value containing ' in it.

I want it to look like this :

=sum({<[BSHeading]={'NCA - dep'n'},[QuarterFiscal]={'Q1'}>} Amount)/1000000)

but the ' sign is causing the problem for quotation marks.

So I tried the below instead :

=sum({$<CFHeading={$(= NCA - dep'n)},[QuarterFiscal]={'Q1'}>} Amount)/1000000

but it does not swon other than 0

If someone has a solution here, I would be very happy!

PS. It could be very useful to know how to handle ' in if clauses as well! DS.

Best Regards

Calle

Re: Set Analysis with ' sign

alexandros17 — Thu, 22 May 2014 13:50:27 GMT

use "NCA - dep'n"

Re: Set Analysis with ' sign

Thu, 22 May 2014 13:54:11 GMT

Use a veriable like this

let V_Name = 'NCA-dep' & chr(39) & 'n';

and then you invoque the variable in the set analisys.

regards.

Marcelo

Re: Set Analysis with ' sign

Anonymous — Thu, 22 May 2014 13:54:37 GMT

I think you'll find that this is not possible. I had a similar issue and had to load the data in the script without the apostrophes

Re: Set Analysis with ' sign

Thu, 22 May 2014 14:00:03 GMT

try something like

=sum({<[BSHeading]={'NCA - dep''n'},[QuarterFiscal]={'Q1'}>} Amount)/1000000)

You have to add an additional single quotes near the single quote in dep'n to make it work.

Re: Set Analysis with ' sign

Thu, 22 May 2014 14:02:24 GMT

Thanks a lot!

That did the trick.

Cheers

Calle

Re: Set Analysis with ' sign

ToniKautto — Thu, 22 May 2014 20:18:20 GMT

Keep in mind to always use double quotes for values in set expressions. This way QlikView will make an exact search for your specified value. I would strongly recommend that you make double quoting a habit for set expressions.

=sum({<[BSHeading]={"NCA - dep'n"},[QuarterFiscal]={"Q1"}>} Amount)/1000000)