Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
=count(distinct(if(len(trim(purgechar(Field1,'-')))>0,Field1)))
=count(distinct(if(istext(replace(left(Field2,1),' ',1)),Field2)))