Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Handling two dates (Canonical date?)

Hi there,

I have a fact table with two dates, one is when the customer registered and another is when a license is registered (basically a purchase date). 

What I want to do is so that you can go in and first find filter down on who registered in a period (customer register date), and then if you want to further filter on what they bought in a certain period (license regsitered).

So I've read this post https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578 and I think I am close, but not sure where I am going wrong

This is what I came up with script wise and I think I am close, but not sure if It this is correct. 

Any help out there?

I added the script below and I added some demo data as an excel sheet if someone else wants to give it a shot.

 

// Calender Sub ////////////////////////////////////

SUB CalendarFromField(_field, _calendar, _prefix)
[$(_calendar)]:
// Generate Final Calendar
LOAD
[$(_field)]
,year([$(_field)]) as [$(_prefix)Year]
,month([$(_field)]) as [$(_prefix)Month]
,day([$(_field)]) as [$(_prefix)Day]
,weekday([$(_field)]) as [$(_prefix)Weekday]
;
// Generate range of dates between min and max.
LOAD
date(DateMin + IterNo()) as [$(_field)] // Link Field
WHILE DateMin + IterNo() <= DateMax
;
// Find min and max of date field values.
LOAD
min(datefield)-1 as DateMin
,max(datefield) as DateMax
;
// Load date field values.
LOAD
FieldValue('$(_field)', RecNo()) as datefield
AutoGenerate FieldValueCount('$(_field)');

END SUB

// Mappings //////////////////////////////////////

CustNo2CustRegDate:
Mapping LOAD Distinct
CustNo,
CustRegDat
FROM [lib://Test/5ca3b4fb-7025-4c38-ba70-1115090c261d.xlsx]
(ooxml, embedded labels, table is Sheet1);

CustNo2CustPurchaseDate:
Mapping LOAD Distinct
CustNo,
PurchaseDate
FROM [lib://Test/5ca3b4fb-7025-4c38-ba70-1115090c261d.xlsx]
(ooxml, embedded labels, table is Sheet1);

Facts:
LOAD
CustNo,
Date(CustRegDat) AS CustomerRegisteredDate,
Date(PurchaseDate) AS PurchaseDate,
"Sum(Amount)"
FROM [lib://Test/5ca3b4fb-7025-4c38-ba70-1115090c261d.xlsx]
(ooxml, embedded labels, table is Sheet1);

DateBridge:
Load Distinct
CustNo,
Date(ApplyMap('CustNo2CustRegDate',CustNo,Null())) AS CanoicalDate,
'RegDate' AS DateType
Resident Facts;

Load Distinct
CustNo,
Date(ApplyMap('CustNo2CustPurchaseDate',CustNo,Null())) AS CanoicalDate,
'PurchaseDate' AS DateType
Resident Facts;

// Call //////////////////////////////////////

CALL CalendarFromField('CanoicalDate', 'CommonCalendar', '');
CALL CalendarFromField('CustomerRegisteredDate', 'CustomerRegisteredDate', 'Registered ');
CALL CalendarFromField('PurchaseDate', 'PurchaseDate', 'Purchased ');

 

1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

Hi

I would add a rowno() as follows

Facts:
LOAD

Rowno() as Canonlink ,
CustNo,
Date(CustRegDat) AS CustomerRegisteredDate,
Date(PurchaseDate) AS PurchaseDate,
"Sum(Amount)"
FROM [lib://Test/5ca3b4fb-7025-4c38-ba70-1115090c261d.xlsx]
(ooxml, embedded labels, table is Sheet1);

then set your the DATEBRIDGE

 DATEBridge:
 Load
CanonLink ,
CustomerRegisteredDate AS DateCanon,
 'Reg' as CanonType
 resident Facts
 ;
 
DATEBridge:
Concatenate (DATEBridge)
 Load
CanonLink ,
PurchaseDate AS DateCanon,  
 'Purchase' as CanonType
 resident Facts
 ;

Then set up your calendar to link with the DateCanon

View solution in original post

7 Replies
Highlighted
Specialist III
Specialist III

Hi

I would add a rowno() as follows

Facts:
LOAD

Rowno() as Canonlink ,
CustNo,
Date(CustRegDat) AS CustomerRegisteredDate,
Date(PurchaseDate) AS PurchaseDate,
"Sum(Amount)"
FROM [lib://Test/5ca3b4fb-7025-4c38-ba70-1115090c261d.xlsx]
(ooxml, embedded labels, table is Sheet1);

then set your the DATEBRIDGE

 DATEBridge:
 Load
CanonLink ,
CustomerRegisteredDate AS DateCanon,
 'Reg' as CanonType
 resident Facts
 ;
 
DATEBridge:
Concatenate (DATEBridge)
 Load
CanonLink ,
PurchaseDate AS DateCanon,  
 'Purchase' as CanonType
 resident Facts
 ;

Then set up your calendar to link with the DateCanon

View solution in original post

Highlighted
Specialist III
Specialist III

CanonDate.JPG

Highlighted
Partner
Partner

Hi,

First of all thank you for the reply! I really appreciate it.

I will try this, but why do you suggest adding the RowNo()? To create a true primary key vs using the custno id?

 

Thanks again,

// Ali A

Highlighted
Specialist III
Specialist III

Hi

If you only have one entry for every CustID you don't need it

But I tend to use '<TABLENAME>' & rowno(0) as FIELDNAME   now just to be sure its a one to one join. As in the past I have had an issue with two tables having the same number.

Highlighted
Partner
Partner

Hi again,

Ended up solving it differently, but this was still a valid solution.

Thank you!
Highlighted
Contributor III
Contributor III

Can you please let us know the solution you followed?

Highlighted
Contributor
Contributor

Could you please let us how you Solved it