topic Re: sub string count in QlikView

sub string count

Tue, 03 Jul 2012 11:38:47 GMT

hi All

my data is attached.

1) my first issue was :

i wanted to count all the Kod (column L) in column (A,B,C, D)

Sum( SubStringCount ( DS_DELAY_SUB_CODE_1&'|'&DS_DELAY_SUB_CODE_2&'|'&DS_DELAY_SUB_CODE_3&'|'&DS_DELAY_SUB_CODE_4, Kod ))

and it works exacctly

2) second issue:

now i want to count the rows if columns (A, B, C, D) dont contain any of Kod(column L)

Can you help me please.

Vincent i wait for your help

Thanks for your answer

Thanks all

vincent_ardiet — Wed, 04 Jul 2012 09:04:04 GMT

Hi,

Don't know why but my first proposition doesn't work because of the SUM.

But there is another problem due to your data because the '?' is used by WildMatch as a joker and it is present in your table.

So, this expression works line by line, but you cannot obtain a global sum :

=if(wildmatch (concat (distinct Kod,'|'),

'*'&replace(DS_DELAY_SUB_CODE_1,'?','-')&'*',

'*'&replace(DS_DELAY_SUB_CODE_2,'?','-')&'*',

'*'&replace(DS_DELAY_SUB_CODE_3,'?','-')&'*',

'*'&replace(DS_DELAY_SUB_CODE_4,'?','-')&'*' )=0,1,0)

It's a first step.

Regards,

Vincent

jonathandienst — Wed, 04 Jul 2012 09:33:29 GMT

If I understand you correctly, you want to count the number of time any of the 4 DS_DELAY_SUB_CODE* exist in the Kod list?

In that case, try this (should work in a table with suitable dimensions or stand-alone)

Sum(Aggr(Sum(If(Exists(Kod, DS_DELAY_SUB_CODE_1) Or Exists(Kod, DS_DELAY_SUB_CODE_2)

Or Exists(Kod, DS_DELAY_SUB_CODE_3) Or Exists(Kod, DS_DELAY_SUB_CODE_4), 1)),

DS_DELAY_SUB_CODE_1, DS_DELAY_SUB_CODE_2,

DS_DELAY_SUB_CODE_3, DS_DELAY_SUB_CODE_4))

Hope that helps

Jonathan

Wed, 04 Jul 2012 11:34:28 GMT

thanks Vincent

Thanks Jonathan

Jonathan i think there is something wrong in the function it doesnt work, exists doesnt work on the design secreen