topic Re: Coalesce Statement in QlikView

Coalesce Statement

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have to apply the coalesce equivalent function on two fields situated in different tables in Qlik. I have tried different tactics such as

statusname&'_'&d2.recordedstatus as status and statusname as stats and d2.recordedstatus as stats but resulted in no fruition. Please guide me towards the right way of doing the coalesce function.

Thanks in advance.......

Re: Coalesce Statement

sunny_talwar — Fri, 04 Mar 2016 23:38:54 GMT

Two things:

1) Would you be able to reduce the size of the application you are sharing? -> Preparing examples for Upload - Reduction and Data Scrambling

2) What is coalesce? Never heard about it, may be you or someone else can explain.

Best,

Sunny

Re: Coalesce Statement

swuehl — Fri, 04 Mar 2016 23:51:40 GMT

Sunny,

it's a SQL function, similar to QV's Alt() function, not limited to numbers.

"Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL."

I haven't looked into the sample QVW, but I assume the first issue to resolve is to reference "two fields situated in different tables in Qlik".

Re: Coalesce Statement

Peter_Cammaert — Sat, 05 Mar 2016 00:21:00 GMT

Hi Sunny, Stefan, I apologise for repeating myself. Again. Just ignore the following...

aaqureshi2010since you decided to launch a new discussion on the same topic, consider closing the previous one as to not leave any loose ends: Coalesce statement in Qlikview‌ Thanks.

Re: Coalesce Statement

Sat, 05 Mar 2016 03:25:24 GMT

Thanks guys for your responses. I have reduced the data size as per Sunny's suggestion and Peter I have closed the above specified thread.

coalesce(statusname,d2.recordedstatus) as status

Please help with the above function.

Thanks,

Re: Coalesce Statement

Sat, 05 Mar 2016 03:43:35 GMT

Sorry needed to remove some unnecessary data. I have commented out the tables not mandatory for the application in the data model.

Re: Coalesce Statement

swuehl — Sat, 05 Mar 2016 09:40:55 GMT

Hm, your DataModel still shows the same size of 41 MB?

I don't believe it's necessary to work with such a large data model to demonstrate your issue. You can filter your records by using a WHERE clause or selecting values in the front end, then reduce to possible values from File menu.

Since you need a script solution, it would be very helpful to be able to reload your document, so we would need the source QVD (or better create some small INLINE tables with some mock up data).

It's also quite unclear to me what the exact requested result is.

I don't really get what you mean with

statusname&'_'&d2.recordedstatus as status and statusname as stats and d2.recordedstatus as stats

I think it would be better if you could translate this requirement in a requested table result, fields and values.

(And again, I assume you can demonstrate what you want to achieve with a couple of records, not some hundred thousand).

Regads,

Stefan

Re: Coalesce Statement

Mon, 07 Mar 2016 13:35:04 GMT

This is my requirement....

coalesce(statusname,recordedstatus) as status.

I have attached a sample Excel sheet with these two columns. I don't know how to implement this in Qlik. Peter had suggested Alt() is the Coalesce in Qlik but only for number fields not for textual fields. My DBA has used this function in his query and wants me to imitate it in Qlik please provide assistance.

Thanks in Advance,

Re: Coalesce Statement

swuehl — Mon, 07 Mar 2016 13:39:59 GMT

Maybe something like

If(Len(Trim( statusname )) >0, statusname, recordedstatus) as status

Len(Trim(FIELD)) does return zero for NULL, but also for empty strings or strings with only spaces.

There is also IsNull(FIELD) function to check only for NULL.

To be able to use above load statement, both fields need to be part of your input table (but that should not be different from COALESCE().

Re: Coalesce Statement

Mon, 07 Mar 2016 13:51:10 GMT

I have one more requirement. I have to imitate when the clientid is null so would this work here

(len(clientid=0)) or should i use isnull(clientid).

Thanks,

Re: Coalesce Statement

jonathandienst — Mon, 07 Mar 2016 14:03:24 GMT

Len(clientid) = 0 returns true for nulls and empty strings

IsNull(clientid) returns true for nulls only

Use whichever one best suits your requirements.

Re: Coalesce Statement

Mon, 07 Mar 2016 14:14:19 GMT

Are there other ways to write ISNULL in qlikview except the two

Len(clientid) = 0 returns true for nulls and empty strings

IsNull(clientid) returns true for nulls only

They both get me the same number of rows but not exactly producing the results needed.

Thanks,

Re: Coalesce Statement

jonathandienst — Mon, 07 Mar 2016 14:21:47 GMT

I suggest that you post a small example the illustrates the problem as well as the value you expect or require.

Those are the only two simple functions, but there may be other ways depending on the context (eg using SQL "NOT IS NULL" in a SQL SELECT statement).

Re: Coalesce Statement

sunny_talwar — Mon, 07 Mar 2016 14:24:24 GMT

Have you tried Len(Trim(clientid))?

Re: Coalesce Statement

Mon, 07 Mar 2016 17:01:22 GMT

Thanks guys for all your help thus far. Below are the SQL functions I have to apply in Qlik.

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

I'm attaching the filters I've written in Qlik. If you see something which jumps out at you guys please provide me with the corrections.

Is Order By only possible in Qlik when you're using Resident Load?

Thanks,

Re: Coalesce Statement

swuehl — Mon, 07 Mar 2016 17:08:14 GMT

If you use in QV

WHERE Len(Trim(netsuidid))

then this is the opposite of (in SQL)

WHERE netsuid is null

You need to use

WHERE Len(Trim(netsuidid)) =0

edit:

WHERE IsNull(netsuidid)

if you want to check only for NULL

Re: Coalesce Statement

Mon, 07 Mar 2016 17:19:35 GMT

Thanks for the help Stefan. You had sent me the below filter earlier today. Is it wise to use this in scripting or as a calculated dimension in a chart. Both those two fields (statusname and recordedstatus) are located in different tables that's why I have opted to use it as a calculated dimension due to my lack of understanding in how to filter it out at the scripting level.

If(Len(Trim( statusname )) >0, statusname, recordedstatus) as status

Re: Coalesce Statement

Mon, 07 Mar 2016 18:26:48 GMT

Thanks all for your kind assistance!!!!!