4 Replies Latest reply: Nov 9, 2012 11:41 AM by Brandon Apperson RSS

    Substring Count returning multiple strings with new Conditional Dimension Functionality

    Brandon Apperson

      Hello everyone,

       

      So recently I tried migrating from Macros to the new conditional Dimension functionality added in QV 11.  However, I have encountered one small problem that I have been unable to overcome without hardcoding.

       

      This issue is if you have two fields like:

       

      BENEFIT

      SPORT_BENEFIT

       

      Then using the conditional dimension it should usually be:

       

      BENEFIT:

      substringcount(concat(%Dimension,'|'),'BENEFIT')

       

      SPORT_BENEFIT:

      substringcount(concat(%Dimension,'|'),'SPORT_BENEFIT')

       

      However as some of you may have already guessed, when you select SPORT_BENEFIT, you also get BENEFIT, as BENEFIT is a substring of SPORT_BENEFIT.

       

      I have tried to use wildmatch but it fails on more than one selection due to the fact that is an exact string match.

       

      I would think to use regular expressions but currently the sub strings in the field names sometimes fall at the beginning, middle or end of them.

       

      The only solution I came up with thus far is hard coding in for BENEFIT:

       

      =(SubStringCount(Concat(%Dimension, '|'), 'BENEFIT') = 2 and SubStringCount(Concat(%Dimension, '|'), 'SPORT_BENEFIT') = 1) or ((SubStringCount(Concat(%Dimension, '|'), 'BENEFIT') = 1 and SubStringCount(Concat(%Dimension, '|'), 'SPORT_BENEFIT') = 0))

       

      etc... but this is going to be a pain in the future if lets say you wanted to add a SCHOOL_BENEFIT field.

       

       

      Any thoughts?

       

      Thanks in advance,

      Brandon