Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP 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
Anonymous
Not applicable
Author

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
Anonymous
Not applicable
Author

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
Author

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,

Anonymous
Not applicable
Author

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)

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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
Author

Thanks guys for all your help.