Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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?

1 Solution

Accepted Solutions
iktrayanov
Creator III
Creator III

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

View solution in original post

8 Replies
iktrayanov
Creator III
Creator III

Can you show us how you generate your time table?

Clever_Anjos
Employee
Employee

A sample of your data would usefull

Not applicable
Author

$(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);

iktrayanov
Creator III
Creator III

It seems to me that you are trying to join timestamps# form your source to time# from your auto-generated table.

Not applicable
Author

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.

Not applicable
Author

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.

iktrayanov
Creator III
Creator III

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

Not applicable
Author

That makes a lot of sense in light of my trial-and-error described above. Thank you for your help!