3 Replies Latest reply: Nov 27, 2017 5:34 PM by Marco Wedel RSS

    Syntax If(match) null

    Antonio Forino

      Hi Guys,

      do you know which is the right syntax?

       

      i need this formula If(Match(FIELD), IS NULL ...

       

       

      How is null should be written? i tried with IsNull but is not working

       

      Here the full syntax

      if (match ((SDURDT),=IsNull),(Date(If(Len(Trim(SDTRDJ))>4,Date(MakeDate(1900+Floor(SDTRDJ/1000))-1+Mod(SDTRDJ,1000))), 'DD/MM/YYYY')),(Date(If(Len(Trim(SDURDT))>4,Date(MakeDate(1900+Floor(SDURDT/1000))-1+Mod(SDURDT,1000))), 'DD/MM/YYYY'))) AS SurgeryDate,

       

      THANK YOU!!

        • Re: Syntax If(match) null
          Felip Drechsler

          Hi Antonio,

           

          You just forgot about the parenthesis of isNull, it shoulbe isNull(), and would be better to do something like:

           

          if

          (

              isNull(SDURDT),    

               (Date(If(Len(Trim(SDTRDJ))>4,Date(MakeDate(1900+Floor(SDTRDJ/1000))-1+Mod(SDTRDJ,1000))), 'DD/MM/YYYY')),

               (Date(If(Len(Trim(SDURDT))>4,Date(MakeDate(1900+Floor(SDURDT/1000))-1+Mod(SDURDT,1000))), 'DD/MM/YYYY'))

          ) AS SurgeryDate,


          Felipe.

          • Re: Syntax If(match) null
            Tamil Nagaraj

            Hi Antonio,

             

            I can see lot of misplaced brackets and date functions in your formula. Could you please attach a sample file to help you further.

            • Re: Syntax If(match) null
              Marco Wedel

              Hi,

               

              one solution might be:

               

              QlikCommunity_Thread_283141_Pic1.JPG

               

              table1:
              LOAD RecNo() as ID, *,
                  Date(MakeDate(1900+Div(If(SDURDT,SDURDT,SDTRDJ),1000))+Mod(If(SDURDT,SDURDT,SDTRDJ),1000)-1) as SurgeryDate
              Inline [
                  SDURDT, SDTRDJ
                  11111, 12345
                  12345,
                        , 89001
                  90010, 91100
                  91100,
                  92123, 93234
                        , 94345
                  94345, 100001
                  100001, 110011
                        ,
                  110111, 117331
                  117331, 123456
                        , 123456
              ];
              

               

              hope this helps

               

              regards

               

              Marco