Function won't strip time off of date

[Lauri]

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:

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.

[swuehl · Accepted solution]

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.

[Lauri]

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!