Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
Contributor III

Re: JOIN failing between autogenerated times and loaded times

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

8 Replies
iktrayanov
Contributor III

Re: JOIN failing between autogenerated times and loaded times

Can you show us how you generate your time table?

Employee
Employee

Re: JOIN failing between autogenerated times and loaded times

A sample of your data would usefull

Not applicable

Re: JOIN failing between autogenerated times and loaded times

$(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
Contributor III

Re: JOIN failing between autogenerated times and loaded times

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

Not applicable

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.

Not applicable

Re: JOIN failing between autogenerated times and loaded times

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
Contributor III

Re: JOIN failing between autogenerated times and loaded times

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

Re: JOIN failing between autogenerated times and loaded times

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

Community Browser