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 in Qlikview

I'm encountering a situation where I would like to use some type of version of coalesce in Qlikview. I've included an example of what I'm trying to do in the Where clause.

date = 2017-03-12

Load

     A,

     B,

     C,

     D,

     E,

From source

     WHERE --> A is not null, then where A = date, otherwise

                        B is not null, then where B = date, otherwise

                        C is not null, then where C = date, otherwise

1 Solution

Accepted Solutions
evan_kurowski
Specialist
Specialist

Seems like everyone in this thread is correct. 

Using the coalesce definition from another software, it says coalesce will return the first number it finds from a list of arguments.

Alt appears to do the same thing. 

If we were only trying to test for a single date format, the following should be a 5 field WHERE clause filter.  (If we're expecting variations in the date formats, the testing for variants would expand)


SET DateFormat='YYYY-MM-DD';

[nums]:
LOAD *
WHERE IsNum(alt(Date(A),Date(B),Date(C),Date(D),Date(E)));
LOAD * INLINE [
    A,B,C,D,E
    feezel, beezel
    Imadate, 2017-03-17
];


Wouldn't mind a COALESCEC (coalesce for characters) function though.  For now it has been If(IsNull(A),B) or If(Len(Trim(A))=0,B)

View solution in original post

4 Replies
MarcoWedel

Where If(IsNull(A),If(IsNull(B),C=date,B=date),A=date)

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

alt(A,B,C) as Date

-Rob

shansundar
Partner - Creator
Partner - Creator

Hello Rob,

I have a doubt the expression you have suggested alt(A,B,C) as Date, it won't result any value or date.

To my understanding Alt function returns the first of the parameters that has a valid numeric Representation (Including dates). If no such match is found, it will return the last parameter (Last value in the list).


Can you correct me if my understanding is not correct.


Thanks,

Shan S

evan_kurowski
Specialist
Specialist

Seems like everyone in this thread is correct. 

Using the coalesce definition from another software, it says coalesce will return the first number it finds from a list of arguments.

Alt appears to do the same thing. 

If we were only trying to test for a single date format, the following should be a 5 field WHERE clause filter.  (If we're expecting variations in the date formats, the testing for variants would expand)


SET DateFormat='YYYY-MM-DD';

[nums]:
LOAD *
WHERE IsNum(alt(Date(A),Date(B),Date(C),Date(D),Date(E)));
LOAD * INLINE [
    A,B,C,D,E
    feezel, beezel
    Imadate, 2017-03-17
];


Wouldn't mind a COALESCEC (coalesce for characters) function though.  For now it has been If(IsNull(A),B) or If(Len(Trim(A))=0,B)