Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bwisealiahmad
Partner - Specialist
Partner - Specialist

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
robert99
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
robert99
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

robert99
Specialist III
Specialist III

CanonDate.JPG

bwisealiahmad
Partner - Specialist
Partner - Specialist
Author

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

robert99
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.

bwisealiahmad
Partner - Specialist
Partner - Specialist
Author

Hi again,

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

Thank you!
mvgadagi
Contributor III
Contributor III

Can you please let us know the solution you followed?

ManikantaSirana549
Contributor
Contributor

Could you please let us how you Solved it