topic Re: Distinct count with complexity in QlikView

Distinct count with complexity

Sun, 15 Feb 2015 05:13:59 GMT

Hi QlikView peeps. I need some assistance with a couple of formulas. I have included a QVW to demonstrate what I'm trying to accomplish.

Essentially I need to do a distinct count on a field while removing nulls and dashes and items that don't start with letters. Please see my notes in the QVW where I get more specific.

Thank you so much!

Example attached.

Re: Distinct count with complexity

Oleg_Troyansky — Sun, 15 Feb 2015 06:32:19 GMT

Hi,

a few comments:

1. Nulls are not counted, but spaces are. If you have values with multiple spaces, they will be counted too.

2. You can count IF() formulas, and in the IF condition, you can list everything you need to include or exclude - spaces, dashes, starting with a letter, etc... If the condition returns False, the result will be Null and it won't get counted.

cheers,

Oleg Troyansky

Re: Distinct count with complexity

maxgro — Sun, 15 Feb 2015 09:04:13 GMT

=count(distinct(if(len(trim(purgechar(Field1,'-')))>0,Field1)))

=count(distinct(if(istext(replace(left(Field2,1),' ',1)),Field2)))