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: 
Mikko88
Partner - Contributor
Partner - Contributor

Qlik Compose - support external calculate formulas?

Hey, does Qlik Compose support external calculate formulas?
I know that I can add new attributes when I create new model or when I create new data mart.
However, example is it possible to add complicated formulas from table column in database?
Or can I maintain all formulas example in database or excel and if it is possbile, how I can use those formulas in data mart?

Labels (2)
1 Solution

Accepted Solutions
Brian_Jones
Employee
Employee

>>Hey, does Qlik Compose support external calculate formulas?

With Qlik Compose, the execution of expression logic is done at the database level. That means the logic is bound by the rules of SQL on your target data warehouse environment. 

Calculation logic that is not SQL based-(e.g. Power Shell or Python scripts) could be added to a Compose project by using a Command Task and scheduling it as part of the workflow. The logic that goes into the Compose model attributes and ETL sets needs to be SQL based.

Brian_Jones_6-1616774340467.png

 

>>I know that I can add new attributes when I create new model or when I create new data mart. However, example is it possible to add complicated formulas from table column in database?

The edit expression dialog you captured is just to get you started and not a compete list of the possible logic that can be accommodated here. Most any SQL logic you would put into a column in the select clause of a SQL statement. Standard database functions that act on a single row can be used. Based on that 'put into a column in the select clause of a SQL statement.' the logic can get somewhat complex, like using standard and user defined database functions if your warehouse supports them, but that has limits. Highly complex formulas can leverage database stored procedures that can be called in Post-Load ETL defined at the mapping level in the Mange ETL Sets screen.  

Brian_Jones_0-1616773723284.png

Brian_Jones_1-1616773748668.png

 

>>Or can I maintain all formulas example in database or excel and if it is possible, how I can use those formulas in data mart?

Since Compose does ELT processing against the database, maintain complex formulas there (e.g. UDFs and Stored Procedures) is a good option if your database supports them. 

Formulas can be used in the data mart either by adding attributes and applying formulas as you did in the model. Here is an example of having added a DaysLate attribute to the fact in a data mart and defining a calculation.

Brian_Jones_4-1616774149306.png

 

Similar to the ETL Sets, database logic can also be added to data mart processing in Pre Loading and Post Loading ETL

Brian_Jones_5-1616774282639.png

 

I hope this helps!

 

 

View solution in original post

3 Replies
Brian_Jones
Employee
Employee

>>Hey, does Qlik Compose support external calculate formulas?

With Qlik Compose, the execution of expression logic is done at the database level. That means the logic is bound by the rules of SQL on your target data warehouse environment. 

Calculation logic that is not SQL based-(e.g. Power Shell or Python scripts) could be added to a Compose project by using a Command Task and scheduling it as part of the workflow. The logic that goes into the Compose model attributes and ETL sets needs to be SQL based.

Brian_Jones_6-1616774340467.png

 

>>I know that I can add new attributes when I create new model or when I create new data mart. However, example is it possible to add complicated formulas from table column in database?

The edit expression dialog you captured is just to get you started and not a compete list of the possible logic that can be accommodated here. Most any SQL logic you would put into a column in the select clause of a SQL statement. Standard database functions that act on a single row can be used. Based on that 'put into a column in the select clause of a SQL statement.' the logic can get somewhat complex, like using standard and user defined database functions if your warehouse supports them, but that has limits. Highly complex formulas can leverage database stored procedures that can be called in Post-Load ETL defined at the mapping level in the Mange ETL Sets screen.  

Brian_Jones_0-1616773723284.png

Brian_Jones_1-1616773748668.png

 

>>Or can I maintain all formulas example in database or excel and if it is possible, how I can use those formulas in data mart?

Since Compose does ELT processing against the database, maintain complex formulas there (e.g. UDFs and Stored Procedures) is a good option if your database supports them. 

Formulas can be used in the data mart either by adding attributes and applying formulas as you did in the model. Here is an example of having added a DaysLate attribute to the fact in a data mart and defining a calculation.

Brian_Jones_4-1616774149306.png

 

Similar to the ETL Sets, database logic can also be added to data mart processing in Pre Loading and Post Loading ETL

Brian_Jones_5-1616774282639.png

 

I hope this helps!

 

 

TimGarrod
Employee
Employee

To add to Brians comments - Compose also provides the ability to create re-usable transformations.  

If you have the same logic you need to apply to multiple places, this is a way to wrap that logic in a "Compose function". 

To Create a re-usable transformation - 

Select the menu in the Model section of Compose - and select Reusable Transformation

TimGarrod_1-1616776617248.png

 

Here is an example of a reusable transformation (this is obviously a very simple example). 

YOu provide the input parameters and the logic.  Note the logic is also expressed in SQL and thus you can use any function that resides in your DW platform. (As Brian said the expression builder provides some commonly used ones, but not all functions of your DW platform).

TimGarrod_0-1616776573159.png

 

The reusable transformation will show up in the expression editor under a new table.

Clicking on it - and it will populate the expression editor (line 1 below).  You would then replace the parameters with the appropriate columns or hardcoded expressions (line 2 below).  Note that you only leave line 2 in the editor 🙂

TimGarrod_2-1616776767683.png

 

 

Additionally in Compose if you have more complex logic that you already have expressed in a user defined function in your data warehouse env - you can leverage that as part of your transformation logic.  (eg. in SQL Server you may have a scalar UDF - dbo.my_udf() - which could be used in the expression editor.

Mikko88
Partner - Contributor
Partner - Contributor
Author

Thank you for the quick answers!
I continue planning, which is best way to start doing calculations in my case.