Substring Count returning multiple strings with new Conditional Dimension Functionality

[#1]

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

[swuehl]

You're %dimension field is a data island, right?

Why not add a field to the this adhoc dimension table with more appropriate (i.e. appropriate for easy filtering) values, maybe just using numbers (2 to the power of n):

LOAD * INLINE [

%Dimension, %DimNum

BENEFIT, 1

SPORT_BENEFIT, 2

SCHOOL_BENEFIT, 4

];

Then, as conditions for the three dimensions:

= sum(%DimNum) bitand 1

= sum(%DimNum) bitand 2

= sum(%DimNum) bitand 4

Hope this helps,

Stefan

[#3]

Yeah this should work fine -- thanks for the quick response.

The only issue I can kind of see is that I have 50 or so dimensions, and so the DimNum field is going to be pretty large by that point.  Was there any way to avoid the overinflation of these values that you can think of?

Thank you again!

[swuehl · Accepted solution]

Right, the proposed solution is maybe not applicable for very large value lists. My bottom line was intended to say:

Just use a connected field for filtering, that hold only values where no value is a sub set of another value in that field.

Using the bit pattern matching only adds a benefit on top, allowing your value matching to be more efficient.

But I think you should be able to use also something like this (just having a continuous autonumbering of %DimNum) for you dimension conditional (which should also be really efficient):

count({<%DimNum *= {1} >} %DimNum)

count({<%DimNum *= {2} >} %DimNum)

count({<%DimNum *= {3} >} %DimNum)

count({<%DimNum *= {4} >} %DimNum)

...

Hope this helps and works out,

Stefan

edit: Oh, maybe you don't need to use a second field if using this method at all.. Just use your field values of %Dimension in the set modifier of %Dimension.

[#5]

Thank you so much!  Works perfectly.