Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Lauri
Specialist
Specialist

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

2 Replies
swuehl
MVP
MVP

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
Specialist
Specialist
Author

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!