topic Re: SubStringCount function issue in QlikView

SubStringCount function issue

vikasmahajan — Thu, 26 May 2016 09:42:19 GMT

Dear all,

I have inline load and loading dimensions into table , and applying =SubStringCount(Concat($Field, '|'), 'Level 4 Mgr Name')

it giving me zero o/p but it should give 1 for ad-hoc reporting.

QVW is attached .

Base on selection in list o/p should 0 /1

Thanks

Vikas

Re: SubStringCount function issue

sunny_talwar — Thu, 26 May 2016 09:43:53 GMT

Try this:

=SubStringCount(Concat(DISTINCT dimension_1, '|'), 'Level 4 Mgr Name')

Re: SubStringCount function issue

sunny_talwar — Thu, 26 May 2016 09:45:35 GMT

Not sure why you are using $Field? because $Field doesn't contain any value of Level 4 Mgr Name

Re: SubStringCount function issue

vikasmahajan — Thu, 26 May 2016 09:45:48 GMT

Thanks Sunny

used this formula still i am not getting results - see attached qvw.

Vikas

Re: SubStringCount function issue

vikasmahajan — Thu, 26 May 2016 09:46:58 GMT

Even if we use dimension_1 still not getting result.

Vikas

Re: SubStringCount function issue

sunny_talwar — Thu, 26 May 2016 09:49:20 GMT

When I open you application, I am seeing this

Are you not seeing this?

Re: SubStringCount function issue

vikasmahajan — Thu, 26 May 2016 09:52:08 GMT

I want when user select 1 and when user de-select 0 for ad-hoc reporting i want this function.

Viikas

Re: SubStringCount function issue

settu_periasamy — Thu, 26 May 2016 09:55:16 GMT

May be this..

=if(GetSelectedCount(dimension_1)>0,

SubStringCount(Concat(DISTINCT dimension_1, '|'), 'Level 4 Mgr Name'),0)

Re: SubStringCount function issue

sunny_talwar — Thu, 26 May 2016 09:56:28 GMT

Try this in that case:

=SubStringCount('|' & GetFieldSelections(dimension_1, '|', 10000) & '|', '|Level 4 Mgr Name|')

Re: SubStringCount function issue

sunny_talwar — Thu, 26 May 2016 09:57:53 GMT

I think it might be better to use GetFieldSelections, rather than using the If with Concat here. Just my opinion

Re: SubStringCount function issue

vikasmahajan — Thu, 26 May 2016 09:59:36 GMT

Thanks Both

Re: SubStringCount function issue

settu_periasamy — Thu, 26 May 2016 10:04:26 GMT

Yes. We can simplify like..

=Alt(WildMatch(GetFieldSelections(dimension_1, '|',100), '*Level 4 Mgr Name*'), 0 )

Re: SubStringCount function issue

vikasmahajan — Thu, 26 May 2016 10:05:01 GMT

Thanks sunny actually you are right if will create a performance issue.

Vikas

Re: SubStringCount function issue

sunny_talwar — Thu, 26 May 2016 10:07:36 GMT

My response seem to go removed, but won't the WildMatch give -1 when the statement is true? We might need to multiply the whole things by -1 to get positive 1?

=Alt(WildMatch(GetFieldSelections(dimension_1, '|',100), '*Level 4 Mgr Name*'), 0) * -1

But I somehow still like using SubStringCount(). My personal preference

Re: SubStringCount function issue

settu_periasamy — Thu, 26 May 2016 10:15:44 GMT

The wildmatch part giving the value '1'. So, i think we don't want to mutiply by -1

Re: SubStringCount function issue

sunny_talwar — Thu, 26 May 2016 10:20:22 GMT

Right, I got it confused with the if. Sorry about that