sub string count

[#1]

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)

and Vincent Ardiet answered it as

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]

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]

Hi

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

[#4]

thanks Vincent

Thanks Jonathan

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