Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Coalesce statement in Qlikview

Hi All,

select distinct d2.clientid, d1c.clientname, coalesce(statusname,d2.recordedstatus) as status, case when activeusercount is not null and activeusercount > 2 then 'yes' else 'no' end as "More than 2 users in 30 days", d1.testaccountflag

where d2.fkdimrbcapplication < 15

and usethisinstance = 1

and netsuiteid is null

order by 1


Can someone please instruct me if we have a Coalesce (postgreSQL)  statement substitute in Qlikview?


And I'm trying to write a if formula for the below but when I get to as it shows an error.


case when activeusercount is not null and activeusercount > 2 then 'yes' else 'no' end as "More than 2 users in 30 days".....=if(Active.activeusercount <> 0 and Active.activeusercount > 2, 'Yes', 'No' as "More than 2 users in 30 days")


Thanks in Advance,

1 Solution

Accepted Solutions

Is this an expression in a chart ?

If so ditch the as .......

     =if(Active.activeusercount <> 0 and Active.activeusercount > 2, 'Yes', 'No' )

For checking null there is the isnull() function

     where isnull(id=0)

View solution in original post

5 Replies

Is your closing bracket misplaced ?

     =if(Active.activeusercount <> 0 and Active.activeusercount > 2, 'Yes', 'No' ) as "More than 2 users in 30 days"

Not applicable

Hi Bill,

Thanks for the reply. No brackets are not the issue here. I'm attaching a sample data set whenever you get a chance.Capture.PNG

One more question Bill is the following the right syntax for null. I've to write an where clause where the id is null

where len(trim(id=0)) would this be enough??

Thanks in Advance,

Is this an expression in a chart ?

If so ditch the as .......

     =if(Active.activeusercount <> 0 and Active.activeusercount > 2, 'Yes', 'No' )

For checking null there is the isnull() function

     where isnull(id=0)

View solution in original post

Keeping a logical path in a discussion is a bit difficult if you post many questions in the same thread. I'll start with your first question.

The Alt() function is a QlikView alternative for the SQL Coalesce() function, but only for numerical values. If you want to check mixed data, you'll have to use nested IF() functions because QlikView has no LOAD equivalent for the SQL CASE WHEN construct.

Best,

Peter

Not applicable

Thanks guys for all your help.