topic Compartmentation in Qlik Sense Table for calculation in App Development

Compartmentation in Qlik Sense Table for calculation

sharanqlik — Mon, 06 Nov 2023 13:02:57 GMT

Hi All,

I am facing a scenario where I need to show measure value only if its not already shown in earlier (or later) row with condition.

Consider below input data -

Product	Theme	Type	Value
A	Credit	Credit	100
A	Credit	Equity	100
A	Credit	General	100
B	Equity	Real Estate	50
B	Equity	General	50

Here Theme of product is always same but Type changes as per sale. The output I want in visualization is -

Product	Theme	Type	Value
A	Credit	Credit	100
A	Credit	Equity	0
A	Credit	General	0
B	Equity	Real Estate	0
B	Equity	General	50

Meaning -

For any product, If Theme = Type then show value ( as first row for product A) else for any product if Theme and Type never match then show value in front of General Type (as second row of product B). Notice that third row of product A is value 0 because there is already a Theme and Type match for product A which is not there in product B.

Re: Compartmentation in Qlik Sense Table for calculation

vincent_ardiet_ — Mon, 06 Nov 2023 15:43:30 GMT

Do you have the ability to modify the loading script? If this is the case are those fields in the same table?

Re: Compartmentation in Qlik Sense Table for calculation

sharanqlik — Tue, 07 Nov 2023 06:42:30 GMT

Hi Vincent,

Yes, We can edit the script.

The columns are from associated tables in data model.

Re: Compartmentation in Qlik Sense Table for calculation

vincent_ardiet_ — Tue, 07 Nov 2023 08:10:42 GMT

Could you describe the datamodel or provide a simplified view only with the 4 fields needed (and the keys linking the different tables involved)?
For example if [Theme] and [Type] are in the same table, first thing to do is to add a flag when their are equal...

Re: Compartmentation in Qlik Sense Table for calculation

sharanqlik — Tue, 07 Nov 2023 08:59:41 GMT

For simplifying the problem, lets say all columns are in same table (like displayed in description) and I am able to add a flag where Theme= Type

Product	Theme	Type	Value	Flag
A	Credit	Credit	100	1
A	Credit	Equity	100	0
A	Credit	General	100	0
B	Equity	Real Estate	50	0
B	Equity	General	50	0

Whats next do you suggest ?

Re: Compartmentation in Qlik Sense Table for calculation

vincent_ardiet_ — Tue, 07 Nov 2023 09:20:35 GMT

Then you can do something like:

mapProductFlag:
Mapping Load Product, Max(Flag) as Flag Resident myTable Group By Product;

myTable2:
Load
*
,if(Flag=1, Value, if(Type='General' and ApplyMap('mapProductFlag',Product)=0, Value,0)) as NewValue
Resident myTable;

Drop myTable;

Re: Compartmentation in Qlik Sense Table for calculation

sharanqlik — Wed, 08 Nov 2023 10:13:12 GMT

Hi Vincent,

Thanks for help. This works like a charm if columns are in single table.

I will need your help now if those are not in same table (which is actual scenario). Please consider below load script

Rating:
Load * inline [
RatingID, Consultant, Type, Product, Rating
1, Alpha, Credit, Car
2, Alpha, Equity, Car
3, Alpha, General, Car
4, Beta, Real Estate, Table
5, Beta, General, Table
];

Product:
Load * Inline [
Product, Theme
Table,Credit
Car, Equity
];

Cost:
Load * Inline [
Product, Value
Table, 100
Car, 50
];

Column 'Type' is in Rating table, 'Theme' is in Product and 'Value' is in Cost. All the three tables are associated with 'Product' field.

How do we proceed here ?

Re: Compartmentation in Qlik Sense Table for calculation

vincent_ardiet_ — Wed, 08 Nov 2023 10:40:00 GMT

You can join everything in the same table, often Qlik Engine prefers this to start schema, but it depends of the other tables you can have. And you can simplify this a bit just joining Rating with Cost, or with Product, up to you. If you want to keep it like this, this is a solution:

Rating:
Load * inline [
RatingID, Consultant, Type, Product, Rating
1, Alpha, Credit, Car
2, Alpha, Equity, Car
3, Alpha, General, Car
4, Beta, Real Estate, Table
5, Beta, General, Table
];

Product:
Load * Inline [
Product, Theme
Table,Credit
Car, Equity
];

Cost:
Load * Inline [
Product, Value
Table, 100
Car, 50
];

mapProductTheme:
Mapping Load Product, Theme Resident Product;

Inner Join (Rating)
Load RatingID, Type=ApplyMap('mapProductTheme',Product) as Flag Resident Rating;

mapProductFlag:
Mapping Load Product, Min(Flag) as FlagGlobal Resident Rating Group By Product;

mapProductValue:
Mapping Load Product, Value Resident Cost;

Rating2:
Load
*
,if(Flag, ApplyMap('mapProductValue',Product)
, if(Type='General' and ApplyMap('mapProductFlag',Product)=0
, ApplyMap('mapProductValue',Product)
,0)) as NewValue
Resident Rating;

Drop Table Rating;

Re: Compartmentation in Qlik Sense Table for calculation

sharanqlik — Wed, 08 Nov 2023 12:42:20 GMT

Hi Vincent,

We can not create one big table with joins as the amount of data is huge and visuals will break.

Considering your solution with applymap. But it will break if we have different costids and need to preserve them to show in visualizations. Consider below updated tables -

Rating:
Load * inline [
RatingID, Consultant, Type, Product, Rating
1, Alpha, Credit, Car
2, Alpha, Equity, Car
3, Alpha, General, Car
4, Beta, Real Estate, Table
5, Beta, General, Table
];

Product:
Load * Inline [
Product, Theme
Table,Credit
Car, Equity
];

Cost:
Load * Inline [
CostID,Product, Value
1,Table, 100
2,Car, 50
3,Table, 300
4,Car, 200
];

Please note that we have more associated tables in data model and these are just sub set of the whole model. We have connected tables via link table logic.

Re: Compartmentation in Qlik Sense Table for calculation

vincent_ardiet_ — Wed, 08 Nov 2023 14:42:23 GMT

Of course if you don't reveal everything 🙂
Try with this, and in your chart expression, do [Value]*[ValueMultiplier]:

Rating2:
Load
*
,if(Flag, 1
, if(Type='General' and ApplyMap('mapProductFlag',Product)=0
, 1
,0)) as ValueMultiplier
Resident Rating;

Re: Compartmentation in Qlik Sense Table for calculation

sharanqlik — Mon, 13 Nov 2023 06:19:13 GMT

Thanks Vincent. This worked as expected.

I am still making some adjustments but this solved the problem, thanks.