Handling two dates (Canonical date?)

[bwisealiahmad]

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 ');

[robert99 · Accepted solution]

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]

[bwisealiahmad]

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]

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]

Hi again,

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

Thank you!

[mvgadagi]

Can you please let us know the solution you followed?

[ManikantaSirana549]

Could you please let us how you Solved it