2 Replies Latest reply: Mar 21, 2018 9:15 AM by Lauri Scharf RSS

    Function won't strip time off of date

    Lauri Scharf

      I've created a function in my Sense load script:

       

      Set DateFix =

      If($1 = 4, makedate(SubField(Replace($2, '  ', ' '), ' ', 3), Match(Left($2, 3), $(mo)), SubField(Replace($2, '  ', ' '), ' ', 2)),

          If($1 = 5, Date(SubField($2, ' ', 1)),

          If($1 = 7, Date(Floor(Timestamp#($2,'YYYY-MM-DD hh:mm:ss tt')), 'M/D/YYYY'),

          $2)));

       

      It reformats dates that come in different formats from different files. I call it in the load:

       

      LOAD

           $(DateFix(ORGID, VisitStartDate)) as VisitStartDate

      Resident aVisit;

       

      My problem: The second If statement (where $1 = 5) does not remove the time, no matter what!  Dates in this file look like this:

       

      5/10/2012 12:00:00 AM


      When I use the same SubField function in the Load statement directly, it works.


      LOAD

           $(DateFix(ORGID, VisitStartDate)) as VisitStartDate,  //Does not strip time

           Date(SubField(VisitStartDate, ' ', 1)) as VisDate          //Successfully strips time

      Resident aVisit;


      Once loaded, VisDate is a date, while VisitStartDate is a timestamp:

      Capture.PNG

      So I'm mystified as to why the same function works in the load script but not in my function, called in the load script.

        • Re: Function won't strip time off of date
          Stefan Wühl

          That's probably because you already loaded values to VisitStartDate with the same numeric value, and Qlik is only capable of storing one textual representation per numeric value / symbol in one field.

           

          You would need use a new field.

            • Re: Function won't strip time off of date
              Lauri Scharf

              That explains it! I just confirmed your answer by changing the field name in the preceding load. (I load the flat file into a temporary table, where I named the field "VisitStartDate." The load described in this post happens next.)

               

              I didn't know that Qlik "remembers" what happened with the first load of a given field name. I assumed that a subsequent use of a field name completely overwrote any previous data. A good lesson!