Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
Where If(IsNull(A),If(IsNull(B),C=date,B=date),A=date)
alt(A,B,C) as Date
-Rob
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
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)