topic Re: Expression query - return 0 if cell is empty in QlikView

Expression query - return 0 if cell is empty

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all. I'm very new to this so don't understand how to use the expressions (or how to interpret examples in the help files). I need to return how many "Prospects" by Referrer. A referrer will have multiple rows if they have referrered multiple people. The name of the person is in the "Prospect Name" column. If the referrer hasn't referrered any people the referrer name is in the spread sheet once and the "Prospect Name" column is empty. For these empty cells I am getting "1" as the count for the number of referrals instead of "0". I have ticked "Show Zero Values" under data handling. What do I need to do to get the correct result? I have attached a small sample of the data. Many thanks in advance for your help.

Re: Expression query - return 0 if cell is empty

vishsaggi — Wed, 01 Feb 2017 23:09:00 GMT

Try this in Pivot table.

Dim:

Referrer Name

Prospect Name

Expre:

= Count(IF(Len(Trim([Prospect Name]))>0, [Referrer Name]))

Re: Expression query - return 0 if cell is empty

Anonymous — Wed, 01 Feb 2017 23:47:58 GMT

Thank you Vishwarath - that's exactly what I needed 🙂

Re: Expression query - return 0 if cell is empty

vishsaggi — Thu, 02 Feb 2017 01:03:35 GMT

Did this not answered your query ?

Re: Expression query - return 0 if cell is empty

Anil_Babu_Samineni — Thu, 02 Feb 2017 05:37:12 GMT

May be alternate approach using Set Analysis

=Count([Prospect Name] = {'$(=Len(Trim([Prospect Name]))>0)'} [Referrer Name])

Re: Expression query - return 0 if cell is empty

Thu, 02 Feb 2017 07:30:06 GMT

Hi ,

use this

=if(len(trim([Prospect Name]))>0,Count([Referrer Name]),0)

regards,

Balakrishnan.R

Re: Expression query - return 0 if cell is empty

sarasunagaram — Thu, 02 Feb 2017 07:49:38 GMT

Hi,

Use this below expression

=Count({$<[Prospect Name]={"=len(Trim([Prospect Name]))>0"}>}[Referrer Name])

Re: Expression query - return 0 if cell is empty

tyagishaila — Thu, 02 Feb 2017 09:12:25 GMT

Try it,

TAB:

LOAD [Referrer Company Name],

[Referrer Name],

[Referral Type],

if(Len(Trim([Prospect Name]))>0,[Prospect Name]) AS [Prospect Name]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Expression: Count([Prospect Name])