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

[#1]

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

[Anonymous]

Can't you just rename the field?

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

[jonathandienst · Accepted solution]

Hi

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

[carvalguil]

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)

[#5]

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