I want the resultant table as below.
|A - AAA||1530|
|B - BBB||1150|
A-AAA includes A, AA and AAA ratings.
B-BBB includes B, BB, and BBB ratings.
In script you can add a new column in table which have condition on Rating column something like -
If Rating like ‘A*’ then ‘A-AAA’
if Rating like ‘B*’ then ‘B-BBB’
Use this field as a dimension and sum of sales should give you results.
Does Qlik Sense provide any way to create measures using such dimensions instead of loading it through table all the time ?
OR creating a temp table to store such values ?
You can create calculated dimension in chart as well with if conditions but would recommend pre calculating in script.
Downside would be that you will require to know this logic beforehand.
If you have only a few Ratings then you can write Condition on Dimensions to get the result.
suppose in your real data if you have many Rating then it will be better to create a condition in the Scripting Level or else you can create a Line line table to group them.
Load * inline
Use Rating Group as Dimension and Expression as Sum(Sales)
LOAD * INLINE [
F1, Rating, Sales
111, AA, 1000
222, AAA, 500
333, A, 30
444, BBB, 50
555, BB, 100
666, B, 1000
Left Join (TAB)
if(wildmatch(Rating, 'A*'),'A-AAA', if(wildmatch(Rating, 'B*'), 'B-BBB', Null())) as New_Rating
Then you can create a simple table Chart: