Can we blend data in Qlik Cloud as we do in Tableau

[Shamanth]

Hi Experts,

I am new to Qlik Cloud, please help me to resolve the below issue!!

I have two fact tables of different granularity, which I want to aggregate before joining in my Data load editor, in tableau it can be done using Data Blending option but Qlik Cloud I am not able to perform the same. Please provide the alternative or work around for the same in Qlik Cloud.

[sbaro_bd]

Hi @Shamanth ,

For this point, Qlik Sense has an advantage on Tableau : the scripting for making some ETL operations. No need a workaround, just use the scripting in data load editor to perfom that.

Regards.

[Steve_Martin]

Hi @Shamanth,

Unfortunately, Tableau treats data sources very different to Qlik. Qlik loads all the data from multiple data sources to create a form of composite model, this way all data is maintained within its original structure very similar to how the Tableau relationship model has worked since v2020.2.

Blends on the other hand are the older method for Tableau to create an aggregated join between two sources, and whilst blends were kinda superseded back in 2015 with the addition of level-of-detail expressions, they still have their benefits albeit, at a much reduced performance due to the need to pre-aggregate the data.

LODx - keep an eye on this as these are functionally similar to the Qlik calc staple - Set Calcs, you might be able to achieve the same using a Set calc, albeit, from the ingested data from your load script.

Also, it'd be an idea to create a composite join field in the tables to help with the calc process later on down the line.

Steve 

[shansen]

I am searching for this same functionality, and have not seen a way that Qlik can do this like Tableau. Let me see if I can describe the use-case a bit better. Say there are two different data tables that have different granularity, but common columns that can link the two after aggregation. The user would like the ability to slice the data for each table before aggregating, so the aggregation and comparison needs to be dynamic.

For example – say you have supply & demand tables. Supply has grain of leads. Demand has grain of client programs. Both are identified with common fields (i.e. country_state, industry). So, you would have to aggregate both by one or both of these fields in order to compare. The supply & demand tables both have some other slicing columns that a user might want to use to refine their comparison (i.e. date fields, etc.). You want to create charts that compare the metrics of supply & demand, and calculate a ratio.

This site: https://community.qlik.com/t5/New-to-Qlik-Sense/Combining-Fields-in-a-Table-Chart-from-Two-Different...
Suggests keeping them as separate tables in the data model – one as a fact table, and the other as an auxiliary table related by a specified field. Then in the front-end you have a chart with the specified field as a dimension, and Sum(metric_from_table1) and Sum(metric_from_table2).
The problem with this is that in order to keep the two tables separate in the data model, you have to name the common fields with different names. Then you have to figure out how to filter the data on both of them based on a single user selection, and display it on the same chart. As far as I know, if the two tables don't have a join in the data model (they do have common fields, but named differently to keep them from joining), Qlik Sense SaaS doesn't know how to relate them to each other in a chart.

Is there a way to do this that I have overlooked? Can Qlik Sense SaaS do this?

[marcus_sommer]

The officially recommended data-model in Qlik is a star-scheme data-model. Means merging (horizontally as well as vertically) all facts into a single-fact-table and providing n dimension-tables. That the facts from various sources may have different field-names and/or data-structures and/or another granularity and the final table may have some degree of unsynchronized data (NULL's) is neither technically nor logically a problem. Nevertheless it would be beneficial to unify everything possible.

Each other kind of data-model will cause much more efforts and requiring a lot more of experience and knowledge without any guarantee of providing better results.

[Katie_MacKenzie]

Why do you need to join them in the data load editor?

[shansen]

I was able to successfully do data blending in Qlik, while allowing dynamic filtering. Although Qlik does not have explicit data blending features per se, using its concatenate functionality and some set expressions accomplishes the same thing. For example, if I have two tables with different grains (fact_supply and fact_demand), I can bring them into a single table with some code like this:

[supply_and_demand]:
LOAD
supply_id,
common_field1,
common_field2,
...
supply_slicer1,
supply_slicer2,
...
supply;
SELECT
supply_id,
common_field1,
common_field2,
...
supply_slicer1,
supply_slicer2,
...
supply
FROM myproject.mydataset.fact_supply;

CONCATENATE (supply_and_demand)
LOAD
demand_id,
common_field1,
common_field2,
...
demand_slicer1,
demand_slicer2,
...
demand;
SELECT
demand_id,
common_field1,
common_field2,
...
demand_slicer1,
demand_slicer2,
...
demand
FROM myproject.mydataset.fact_demand;

This is not very efficient, as it puts NULLs in the supply_slicer columns for all the demand rows and NULLs in the demand slicer columns for all the supply rows, but at least everything is in one table. This makes it so supply and demand can be displayed in the same chart, each aggregated according to common_field1, common_field2, etc., and these can be dynamically filtered. You just have to setup some variables and supply and demand measures (or Master Measures) with formulas like these:

demand_sum = {<common_field1 = {'$(vCF1)'}, common_field2 = {'$(vCF2)'}, supply_slicer1=, supply_slicer2=>} Sum(demand)
supply_sum = {<common_field1 = {'$(vCF1)'}, common_field2 = {'$(vCF2)'}, demand_slicer1=, demand_slicer2=>} Sum(supply)
where vCF1 and vCF2 are variables whose value is controlled by a Variable input.
The slicer= statements make it so the demand rows don't get filtered out when filtering selections are made to supply slicers, and vice versa.

So, that is how I did data blending in Qlik. Enjoy!