topic Re: Handling time from 2 fact tables in App Development

Handling time from 2 fact tables

RUNIL — Tue, 14 Jan 2020 11:29:41 GMT

Dear Team, How to handle time from 2 tables? Dates are connected to Master calendar the same way i need to connect Date Time field from 2 fact tables to display the transactions on hourly basis. I will create the hour bucket for the time field. When i select the Month 1st Jan-2020, then it should show the values from both tables which are fallen in the same time. Thanks in Advance.

Re: Handling time from 2 fact tables

dplr-rn — Tue, 14 Jan 2020 14:53:23 GMT

the problem seems similar to issue where have multiple data columns and want select once.

In such cases canonical calendar helps.

if you extend that to include time as well it should work.

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

Re: Handling time from 2 fact tables

RUNIL — Wed, 15 Jan 2020 07:03:51 GMT

Thanks for reply Ranjith,

Date field is coming with time as well from all the field. I passed only date and connected to Master calendar. But i need time also now from two tables. This time should be common for that two tables. If i select one particular date from Master calendar, i should see the time on that date from both tables.

Canonical calendar will work where there is 2 dates to handle.

Is this below correct approach?

Table:

Floor(Orderdate) as Date,

Timestamp(Timestamp#(Orderdate,'MM/DD/YYYY hh:mm:ss TT'),'MM/DD/YYYY hh:mm:ss') as DateTime.

Can i connect this date to Master calendar and DateTime field to Canonical Calendar? Same this from one more table.