8 Replies Latest reply: Jan 16, 2014 5:21 PM by Stephen Daspit RSS

    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.

       

      How can I force the autogenerated times to join?