topic Re: Coalesce statement in Qlikview in QlikView

Coalesce statement in Qlikview

Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Coalesce statement in Qlikview

Anonymous — Thu, 03 Mar 2016 22:50:06 GMT

Is your closing bracket misplaced ?

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

Re: Coalesce statement in Qlikview

Thu, 03 Mar 2016 23:00:09 GMT

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.

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,

Re: Coalesce statement in Qlikview

Anonymous — Thu, 03 Mar 2016 23:08:58 GMT

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)

Re: Coalesce statement in Qlikview

Peter_Cammaert — Thu, 03 Mar 2016 23:15:33 GMT

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

Re: Coalesce statement in Qlikview

Sat, 05 Mar 2016 03:02:20 GMT

Thanks guys for all your help.