Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello. I am loading data from an external text file that has a field containing full timestamps as its values. The format of that external field is 'YYYY-MMM-DD hh:mm:ss.fff'. The timestamps do go all the way out to milliseconds.
As far as I can tell, I successfully parse that timestamp into just a time in the load script by using FRAC(TIMESTAMP#([external_field],'YYYY-MMM-DD hh:mm:ss.fff')). Note that I don't force Qlikview to do any kind of formatting on that value. It shows up as an ugly decimal, which makes sense.
I don't really care about the milliseconds, but I want to round those down to the nearest whole second. I do that with FLOOR([value],1/(24*60*60)), where [value] is the same FRAC(TIMESTAMP#([external_field],'YYYY-MMM-DD hh:mm:ss.fff')) from above. In the code, my FLOOR function is FLOOR(FRAC(TIMESTAMP#([external_field],'YYYY-MMM-DD hh:mm:ss.fff')),1/(24*60*60)). I just broke it into two pieces in this paragraph for clarity's sake. Just as before, I'm left with an ugly decimal because I don't force any kind of formatting. I've manually verified (in several different manners) that this is all working; millisecond-level times that belong to the same whole second are successfully aggregating further down in the script.
My challenge now is that I'm left with a data set having whole-second-level time granularity, but not every second is represented. I need to fill in the blanks. I've autogenerated a table of times (down to the whole second), but QV doesn't see the time values in this autogenerated table as being the same as the time values I've parsed in the load script. When I do a left join into the autogenerated table, I would expect to pull in the previously-loaded data where the whole-seconds match. The join fails to pull most of the values in (and yes, the field names are the same). When I don't join, the subset ratio is <1%, so clearly there some QV funniness going on behind the scenes with data/types on the autogenerated table.
How can I force the autogenerated times to join?
the difference between Time() and Time#() may be the explanation
Time#('8/6/97 09:00:00',TimeFormat)
retuns => 8/6/97 09:00:00
Time('8/6/97 09:00:00',TimeFormat)
retuns => 09:00:00
Can you show us how you generate your time table?
A sample of your data would usefull
$(tHours):
NOCONCATENATE
LOAD
ROWNO()-1 AS '$(fHour)'
AUTOGENERATE 24
;
$(tMinutes):
NOCONCATENATE
LOAD
ROWNO()-1 AS '$(fMinute)'
AUTOGENERATE 60
;
$(tSeconds):
NOCONCATENATE
LOAD
ROWNO()-1 AS '$(fSecond)'
AUTOGENERATE 60
;
$(tFinalTimes):
NOCONCATENATE
LOAD
TEXT(IF(LEN($(fHour))=2,$(fHour),0 & $(fHour))) AS '$(fHour)'
RESIDENT $(tHours)
;
DROP TABLE $(tHours);
JOIN($(tFinalTimes))
LOAD
TEXT(IF(LEN($(fMinute))=2,$(fMinute),0 & $(fMinute))) AS '$(fMinute)'
RESIDENT $(tMinutes)
;
DROP TABLE $(tMinutes);
JOIN($(tFinalTimes))
LOAD
TEXT(IF(LEN($(fSecond))=2,$(fSecond),0 & $(fSecond))) AS '$(fSecond)'
RESIDENT $(tSeconds)
;
DROP TABLE $(tSeconds);
RENAME TABLE $(tFinalTimes) TO $(tTempFinalTimes);
$(tFinalTimes):
NOCONCATENATE
LOAD
TIME#($(fHour) & ':' & $(fMinute) & ':' & $(fSecond),'$(cWorkingTimeFormat)') AS '$(fGeneratedStamp)'
RESIDENT $(tTempFinalTimes)
ORDER BY $(fHour) ASC, $(fMinute) ASC, $(fSecond) ASC
;
DROP TABLE $(tTempFinalTimes);
It seems to me that you are trying to join timestamps# form your source to time# from your auto-generated table.
so... I've been throwing many different functions at the wall to see what sticks... wrapping functions inside of functions with FRAC() and FLOOR() and TIME() and TIME#() etc...
I think I got it to work by wrapping everything inside on an extra FRAC(MAKETIME(NUM#([time value that I thought was already parsed]),NUM#([time value that I thought was already parsed]),NUM#([time value that I thought was already parsed]))).
To be frank, I have no idea why this is working, but so far, it seems to work. I used the same nested wrapper of functions on both my autogenerated times and the already-parsed piece of my external time field.
I used FRAC() on the initial load to get rid of the date piece of the timestamp, but I think the resulting number from that wasn't exactly the same as the autogenerated time values.
the difference between Time() and Time#() may be the explanation
Time#('8/6/97 09:00:00',TimeFormat)
retuns => 8/6/97 09:00:00
Time('8/6/97 09:00:00',TimeFormat)
retuns => 09:00:00
That makes a lot of sense in light of my trial-and-error described above. Thank you for your help!