Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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!