JOIN failing between autogenerated times and loaded times
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.
Re: JOIN failing between autogenerated times and loaded times
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.