Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Distinct count with complexity

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.

2 Replies
MVP & Luminary
MVP & Luminary

Re: Distinct count with complexity

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

MVP
MVP

Re: Distinct count with complexity

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

1.jpg

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

2.jpg