Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

laurischarf
Contributor II

Function won't strip time off of date

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.

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Function won't strip time off of date

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.

2 Replies
MVP
MVP

Re: Function won't strip time off of date

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.

laurischarf
Contributor II

Re: Function won't strip time off of date

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!

Community Browser