Creating link and date tables in Qlik Sense

Creating link and date tables in Qlik Sense.qvs

// ============================  LOAD TABLES  ============================

/*  LOAD Fact table 1, including creating the compound key that will be used 
    to link this table to the other ones
*/
Qualify *;
Unqualify [%mainKey], [CASENUMBER], [EMPLOYEE_CUSTOMERNO];

[ReqAb]:
LOAD
    AutoNumber(
    	[ABSENCE_CASENUMBER]	& '|' &
        [EMPLOYEE_CUSTOMERNO]
    ) as [%mainKey],
    
    [ABSENCE_CASENUMBER] AS [CASENUMBER],
    [EMPLOYEE_CUSTOMERNO],
    
    NOTIFICATION_CASENUMBER,
    ABSENCE_CASETYPENAME,
    ABSENCE_CASESTATUS,
    ABSENCE_CASEOWNER,
    date(Ceil([ABSENCE_CASECREATIONDATE])) as [ABSENCE_CASECREATIONDATE],
    date(Ceil([ABSENCE_CASELASTUPDATEDATE])) as [ABSENCE_CASELASTUPDATEDATE],
    ABSENCE_INTAKESOURCE,
    ABSENCE_NOTIFIEDBY,
    // ...
From [someFolderConnection/table1]();
Unqualify *;

/*  LOAD Fact table 2, though we don't create the compound key since this table only
    has a single key field
*/
Qualify *;
Unqualify [CASENUMBER];

[VBI]:
LOAD
	[CASENUMBER],
    
    CLASSID,
    INDEXID,
    date(Ceil([LASTUPDATEDATE])) as [LASTUPDATEDATE],
    UPDATEDBYUSERID,
    date(Ceil([CREATIONDATE])) as [CREATIONDATE],
    CREATEDBYUSERID,
    // ...
From [someFolderConnection/table2]();
Unqualify *;


// ========================  CREATE DATE TABLE  ========================

/*  Create new table with key and date fields from table 1  */
[Requested Absence Dates]:
CrossTable ([Date Field], [Date], 2) Load
    [CASENUMBER]
  , [EMPLOYEE_CUSTOMERNO]
  , [ReqAb.ABSENCE_CASECREATIONDATE]
  , [ReqAb.ABSENCE_CASELASTUPDATEDATE]
  , [ReqAb.LEAVEREQUEST_NOTIFICATIONDATE]
  , [ReqAb.LEAVEREQUEST_LASTUPDATEDATE]
Resident [Requested Absence];

/*  Drop the date fields from table 1 since we no longer need them  */
Drop Fields
    [ReqAb.ABSENCE_CASECREATIONDATE]
  , [ReqAb.ABSENCE_CASELASTUPDATEDATE]
  , [ReqAb.LEAVEREQUEST_NOTIFICATIONDATE]
  , [ReqAb.LEAVEREQUEST_LASTUPDATEDATE]
From [Requested Absence];

/*  Create new table with key and date fields from table 2  */
[VBI Dates]:
CrossTable ([Date Field], [Date], 1) Load
    [CASENUMBER]
  , [VBI.LASTUPDATEDATE]
  , [VBI.CREATIONDATE]
  , [VBI.RECEIVEDDATE]
Resident [VBI];

/*  Drop the date fields from table 1 since we no longer need them  */
Drop Fields
    [VBI.LASTUPDATEDATE]
  , [VBI.CREATIONDATE]
  , [VBI.RECEIVEDDATE]
From [VBI];


/*  Rename the first date table and then join the second date table to it to result 
    in a single Date table 
*/
Rename Table [Requested Absence Dates] to [Dates load];

Join ([Dates load]) LOAD * Resident [VBI Dates];
Drop Table [VBI Dates];


/*  Finally, load that Date table again and create the compound key that will be used 
    to link this table to the other ones
*/
[Dates]:
Load DISTINCT
	AutoNumber(
    	[CASENUMBER]			    & '|' &
      [EMPLOYEE_CUSTOMERNO]
    ) as [%mainKey]
    
  , [Date Field]
  , [Date]
Resident [Dates load];

Drop Table [Dates load];


// ========================  CREATE LINK TABLE  ========================

/*  Create the initial link table by loading the key fields table 1  */
[Link Table load]:
LOAD DISTINCT
    [CASENUMBER],
    [EMPLOYEE_CUSTOMERNO]
Resident [Requested Absence];

/*  Drop the key fields from table 1 since they're no longer needed. We'll be
    using the compound key to associate this table with the other ones, via
    the Link table
*/
Drop Fields
    [CASENUMBER],
    [EMPLOYEE_CUSTOMERNO]
From [Requested Absence];


/*  Join the key field from table 2 into the new link table. We don't need to
    drop the key field from this table since it already only has one key field
    to begin with
*/
JOIN LOAD DISTINCT
    [CASENUMBER]
Resident [VBI];

/*  Finally, load that Link table again and create the compound key that will be used 
    to link this table to the other ones
*/
[Link Table]:
LOAD DISTINCT
	AutoNumber(
    	[CASENUMBER]			& '|' &
        [EMPLOYEE_CUSTOMERNO]
    ) as [%mainKey]
    
  , [CASENUMBER]
  , [EMPLOYEE_CUSTOMERNO]
Resident [Link Table load];

Drop Table [Link Table load];