Skip to main content

Qlik Compose for Data Warehouses

Discussion board for collaboration on Qlik Compose for Data Warehouses.

cancel
Showing results for 
Search instead for 
Did you mean: 
Pierre_G
Contributor III
Contributor III

Qlik Compose relationship with hard-coded value

Source system has 1 status table that contains all statuses for multiple tables. How can this be implemented in Qlik Compose ?  

Example: 

Select * from Incident
join Status on incident.status_cde = status.status_cde and status.object_type_cde='Incident'

Select * from webform
join Status on webform.status_cde = status.status_cde and status.object_type_cde='Webform'

A hard-coded value cannot be specified in the Model relationship...

 

 

Labels (1)
1 Solution

Accepted Solutions
TimGarrod
Employee
Employee

There are 2 ways to handle this in Compose. 

Option 1 -   

        Model specific "LOOKUP" tables that represent each type of lookup.   In your simplified scenario, that would mean having a "Incident_Status"  and "Webform_Status" - and in the mapping for the 2 status tables, add the filter for "incident" and "webform" types.    
   Benefit of this, is any dimension etc. you create in the data mart has a well defined set of initial data and the universal lookup table is broken into its specific domain parts.    Con - you have to manage multiple tables in the DWH Model. 

 

Option 2 -   Have 1 "Status" table.   The "hard-coded"value would be handled in the ETL.   e.g. the Status table PK would need to include "status_cde" and "object_type_cde".  In the mapping for "Incident" - you would have status_cde mapped, and would hard code 'Incident' for the object_type_cde.   Likewsie for 'Webform'

 

View solution in original post

1 Reply
TimGarrod
Employee
Employee

There are 2 ways to handle this in Compose. 

Option 1 -   

        Model specific "LOOKUP" tables that represent each type of lookup.   In your simplified scenario, that would mean having a "Incident_Status"  and "Webform_Status" - and in the mapping for the 2 status tables, add the filter for "incident" and "webform" types.    
   Benefit of this, is any dimension etc. you create in the data mart has a well defined set of initial data and the universal lookup table is broken into its specific domain parts.    Con - you have to manage multiple tables in the DWH Model. 

 

Option 2 -   Have 1 "Status" table.   The "hard-coded"value would be handled in the ETL.   e.g. the Status table PK would need to include "status_cde" and "object_type_cde".  In the mapping for "Incident" - you would have status_cde mapped, and would hard code 'Incident' for the object_type_cde.   Likewsie for 'Webform'