topic Re: having problem with ' in a 'String' and doesn't work with Chr(39) in QlikView

having problem with ' in a 'String' and doesn't work with Chr(39)

Fri, 03 Oct 2014 13:29:46 GMT

Hello,

I am trouble with a record name which having '

This is name of the field -------- Care Transitions (reporting Oct '14)

I wrote an expression like this :

=if(DChart='Care Transitions (reporting Oct '&chr(39)&'14)',count({< [CAHPS Count Flag]={1}, [CAHPS Chart Domain]={'Care Transitions (reporting Oct '&chr(39)&'14)'}, [CAHPS Questions Flag]={1}, [$(vDateType) $(vDateUnit)],

[Numeric $(vDateType) $(vDateUnit)]={">=$(=num(Max([Numeric Previous-x $(vDateType) $(vDateUnit)])))<=$(=Max([Numeric $(vDateType) $(vDateUnit)]))"}

>} Distinct [Survey ID]),0)

This expression is working with other filed names but not for the 'Care Transitions (reporting Oct '14)' . I tried to replace single qoute in the filed name with chr(39) but no luck.

Is there any mistake in that expression?? Please help

Thanks in advance!!

Re: having problem with ' in a 'String' and doesn't work with Chr(39)

Anonymous — Fri, 03 Oct 2014 13:41:01 GMT

Can't you just rename the field?

The value of having that ' in the field name doesn't worth the effort, IMO.

Re: having problem with ' in a 'String' and doesn't work with Chr(39)

jonathandienst — Fri, 03 Oct 2014 13:42:34 GMT

I suggest that you alias your field names into something more suitable for Qlikview and Qlikview expressions. It will make the expressions clearer as well easier to write and debug.

To search for something containing a quote, you could use WildMatch or a wildcard search in set analysis:

If(WildMatch(DChart, 'Care Transitions (reporting Oct *14)'), ....

...[CAHPS Chart Domain]={"Care Transitions (reporting Oct *14)"}, ...

I would avoid using dynamic field names and rather rely on the data model design to do the filtering. Without seeing your model, I am merely guessing, but I suspect that this expression does not perform very well.

HTH

Jonathan

Re: having problem with ' in a 'String' and doesn't work with Chr(39)

carvalguil — Fri, 03 Oct 2014 13:43:43 GMT

Your problem is in the Set Analysis.

I haven't tried this one. But check the expression below. If it's not working, this is the way.

if(DChart='Care Transitions (reporting Oct '&chr(39)&'14)',count({< [CAHPS Count Flag]={1}, [CAHPS Chart Domain]={"$(='Care Transitions (reporting Oct '&chr(39)&'14)')"}, [CAHPS Questions Flag]={1}, [$(vDateType) $(vDateUnit)],

[Numeric $(vDateType) $(vDateUnit)]={">=$(=num(Max([Numeric Previous-x $(vDateType) $(vDateUnit)])))<=$(=Max([Numeric $(vDateType) $(vDateUnit)]))"}

>} Distinct [Survey ID]),0)

Re: having problem with ' in a 'String' and doesn't work with Chr(39)

Fri, 03 Oct 2014 14:19:09 GMT

Thank you Jonathan. Its just working fine now

Iam just using this for one chart. so that the user can make selections on the chart and it doesn't reflect to other tables or charts. So I created an inline with name Dchart and trying to match them with CAHPS Chart domain. Now I endedup with a big expression. But still its nt that bad.

If you have any suggestions for me please let me know.

Many thanks!!

Mon