Coalesce Statement

[#1]

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.......

[sunny_talwar]

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

[swuehl]

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".

[Peter_Cammaert]

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.

[#5]

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,

[#6]

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

[swuehl]

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

[#8]

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,

[swuehl · Accepted solution]

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().

[#10]

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,