Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
chrissybrown
Contributor
Contributor

Expression query - return 0 if cell is empty

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.

7 Replies
vishsaggi
Champion III
Champion III

Try this in Pivot table.

Dim:

Referrer Name

Prospect Name

Expre:

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

chrissybrown
Contributor
Contributor
Author

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

vishsaggi
Champion III
Champion III

Did this not answered your query ?

Anil_Babu_Samineni

May be alternate approach using Set Analysis

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable

Hi ,

use this

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

regards,

Balakrishnan.R

sarasunagaram
Partner - Contributor II
Partner - Contributor II

Hi,

Use this below expression

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

tyagishaila
Specialist
Specialist

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])