Count if expression

[#1]

Hi,

I'm new to Qlikview and have come across a blocker that I've not been able to get around.

I want to carry out a variable count of one column and based on that outcome carry out a Count DISTINCT of another column.

To put into context;

MFPN          Unique ID

1                         9

2                         NULL

3                         8

1                         NULL

1                         7

2                         6

I want to look at Unique ID and where the value doesn't = NULL I want to carry out a Distinct Count of MFPN

I have tried

Count (DISTINCT MFPN (if ([Unique ID], (not 'NULL'), ([Unique ID])))

I know its wrong but its as close as I can get!!

Any help will be apprecited.

Thanks,

Phil

[rajni_batra]

Try

Count (DISTINCT MFPN (if ([Unique ID]<> 'NULL'), ([Unique ID])))

[#3]

Thanks Rajni, however, that throws back an error with the use of the 'if' expression.

Any thoughts?

[rajni_batra]

can u exactly show  me what result r u expecting exactly from this kind of expression

[#5]

In the context of the example I gave it should bring back a count of 4 as 2 of the MFPN's have NULL in there corresponding Unique ID field and I want to exclude the NULL values from the DISTINCT Count.

Does that help?

[rajni_batra]

=count(if([Unique ID]<>'NULL',[Unique ID]))

so i think it would be good enough

[whiteline · Accepted solution]

=count({$<[Unique ID]-={"NULL"}>}  [Unique ID])

[#8]

Thanks Whiteline. Worked perfectly