common date issue

[Manni_SM]

Hi All,

i have transaction date in two tables   and in UI i need to use   as from date and todate   as single date field

 so final date field have all dates  so how to create?

and  in UI i have two straight tables  one table will be single table  and second stright  table will have  table2  information

Table 1:

load 

a

b

c

Trandate

Tabl2:

a

b

c

d

Trandate

UI: 

fromdate: trandate

todate:     trandate    ( these trandate will be common values)

[Greg_Taffer]

To use transaction dates from two different tables as a single date field in your Qlik UI, you should create a Canonical Date (also known as a Date Bridge). This allows one master calendar to control multiple tables. 

1. Create a Date Bridge Table 

In your data load script, create a bridge table that collects the dates from both Table 1 and Table 2 into a single common field (e.g., CanonicalDate).

DateBridge:
// Load from Table 1
LOAD 
   Trandate AS CanonicalDate,
   'Table 1' AS DateType,
   ID_Field  // Use a unique ID to link back to Table 1
RESIDENT Table1;

CONCATENATE (DateBridge)
// Load from Table 2
LOAD 
   Trandate AS CanonicalDate,
   'Table 2' AS DateType,
   ID_Field  // Use a unique ID to link back to Table 2
RESIDENT Table2;

• Key Concept: Each table must have a unique key (like an ID) to link correctly to this bridge.
• DateType: Adding this field allows you to filter the UI specifically for "Table 1" or "Table 2" dates if needed. 

2. Create the Master Calendar

Build your Master Calendar based on the CanonicalDate field from the bridge table. This ensures the UI "From Date" and "To Date" filters see all dates from both tables. 

MasterCalendar:
LOAD
   CanonicalDate,
   Year(CanonicalDate) AS Year,
   Month(CanonicalDate) AS Month,
   Day(CanonicalDate) AS Day
RESIDENT DateBridge;
 

3. UI Implementation

• Filters: Use the CanonicalDate (or its Year/Month derivatives) in your Filter Panes to act as your "From" and "To" selection.
• Straight Tables:
  • Table 1 Chart: Display fields a, b, c. It will automatically filter when you select a date because it's linked through the DateBridge.
  • Table 2 Chart: Display fields a, b, c, d. It will also filter simultaneously based on the same date selection. 

[Manni_SM]

Thanks for the reply Greg_Taffer

if i have 3 date fields shall i add another resident table? or how i can use with 3date fields with one single filter in UI

[marcus_sommer]

At first you need to consider which relationship the dates have to each other (and usually the entire data-set). Quite often it's only a single date because dates from sources like sales/forecast/budget are the same as well as from orders/billing/shipment - just the point of view/journey is a different one.

This means not the same date-information should be loaded n times as another field, causing rather more than less efforts to link and synchronize them, else the sources itself needs to merged properly (mostly with a concatenate logic into a single fact-table).

[Manni_SM]

Hi @Greg_Taffer  @marcus_sommer   i tried below is it correct way?

DateBridge:

LOAD
date(eff_date) AS CanonicalDate,
ID
RESIDENT Alerts;

CONCATENATE (DateBridge)

LOAD
date(date_rev) AS CanonicalDate,

ID
RESIDENT Alerts;

CONCATENATE (DateBridge)
LOAD
acct_date AS CanonicalDate,

ID
RESIDENT Alerts;

CONCATENATE (DateBridge)

LOAD
date(acct_revo) AS CanonicalDate,

ID
RESIDENT Alerts;

[marcus_sommer]

As far as all fields are real dates and no strings or timestamps and the format is identically (also by acct_date ?) the approach combined the 4 fields into a single date-field.

If it's useful from logically point of view respectively if it supports the wanted views couldn't be answered.