4 Replies Latest reply: Mar 26, 2017 11:33 PM by Evan Kurowski RSS

    Coalesce in Qlikview

    Seth Rosenbauer

      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

        • Re: Coalesce in Qlikview
          Marco Wedel

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

          • Re: Coalesce in Qlikview
            Rob Wunderlich

            alt(A,B,C) as Date

             

            -Rob

              • Re: Coalesce in Qlikview
                Shanmuga S

                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

              • Re: Coalesce in Qlikview
                Evan Kurowski

                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)