Compartmentation in Qlik Sense Table for calculation

[sharanqlik]

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.

[vincent_ardiet_]

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

[sharanqlik]

Hi Vincent,

Yes, We can edit the script.

The columns are from associated tables in data model.

[vincent_ardiet_]

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...

[sharanqlik]

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 ?

[vincent_ardiet_]

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;

[sharanqlik]

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 ?

[vincent_ardiet_]

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;

[sharanqlik]

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. 

[vincent_ardiet_ · Accepted solution]

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;