Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
AHMB
Contributor III
Contributor III

Time field in "Master Time" table and Time field in fact table looks equal but are not linked

Hi,

I have this snippet that generates the Master Time table which has all minutes in the day and from that generates hours field:

 

Master_time:
Load *,
 Dual(Time(Floor(Time#([Order Time], 'hh:mm TT')), 'hh TT') ,hour([Order Time])) as [Order Hour]  
;
LOAD
	time(recno()/1440, 'hh:mm TT') as [Order Time],
    time(recno()/1440, 'hh:mm TT') as [Order Time2],  
    minute(recno()/1440) as [Order Minute]
autogenerate(1440); // Generates every 1 minute

(I added [Order Time2] to be able to add it side by side with [Order Time] in a table box for demonstration)

and this line in the fact table extracts the time from ORDER_DATE and gets rid of the seconds.

Time(floor(frac(ORDER_DATE),1/24/60),'hh:mm TT') as [Order Time]

 

AHMB_0-1722541364328.png

this should work, however, not all "times" values are linked it seems

 

here is a table box where I added [Order Time] and [Order Time2] and not all values are linked:

AHMB_1-1722541626763.png

I even converted both fields to numbers to see the underlying values but that didn't help

What is going on? why aren't the values linked properly?

 

 

Labels (2)
1 Reply
marcus_sommer

The time-generation between both sources is (slightly) different because the time-table field based on a pure division while the fact-table field has with floor(field, 1/24/60) a rounding. Just apply the rounding to the time-table, too and it should be working.