topic Function won't strip time off of date in App Development

Function won't strip time off of date

Lauri — Tue, 20 Mar 2018 20:16:15 GMT

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.

Re: Function won't strip time off of date

swuehl — Tue, 20 Mar 2018 20:38:58 GMT

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 — Wed, 21 Mar 2018 13:15:56 GMT

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!