topic Re: How can I get aggregated value ? in QlikView

How can I get aggregated value ?

tdiwanji — Wed, 25 Nov 2020 16:16:04 GMT

custID	Rating	Sales
111	AA	1000
222	AAA	500
333	A	30
444	BBB	50
555	BB	100
666	B	1000

I want the resultant table as below.

Rating	Total Sales
A - AAA	1530
B - BBB	1150

A-AAA includes A, AA and AAA ratings.

B-BBB includes B, BB, and BBB ratings.

Kindly help.

Re: How can I get aggregated value ?

thakkarrahul01 — Thu, 20 Sep 2018 03:39:04 GMT

‌HI Tapan,

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.

Regards,

Rahul

Re: How can I get aggregated value ?

tdiwanji — Thu, 20 Sep 2018 03:41:19 GMT

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 ?

Re: How can I get aggregated value ?

thakkarrahul01 — Thu, 20 Sep 2018 03:48:55 GMT

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

Re: How can I get aggregated value ?

mdmukramali — Thu, 20 Sep 2018 05:48:39 GMT

Hi,

If you have only a few Ratings then you can write Condition on Dimensions to get the result.

Like :

Dimesion:

=if(Left(Rating,1)='A','A-AAA',

if(Left(Rating,1)='B','B-BBB'))

=PICK(Match(Rating,'A','AA','AAA','B','BB','BBB'),'A-AAA','A-AAA','A-AAA','B-BBB','B-BBB','B-BBB')

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.

like:

Load * inline

[

Rating,Rating_Group

A,A-AAA

AA,A-AAA

AAA,A-AAA

B,B-BBB

BB,B-BBB

BBB,B-BBB

]

;

Use Rating Group as Dimension and Expression as Sum(Sales)

Thanks,

Mohammed Mukram

Re: How can I get aggregated value ?

vishweshwarisun — Thu, 20 Sep 2018 06:06:09 GMT

Hi,

You can use the below expression as your dimension and use sum(Sales) as you measure

pick(WildMatch(Rating,'A*','B*'),'B-BBBB','A-AAA')

Re: How can I get aggregated value ?

tdiwanji — Thu, 20 Sep 2018 06:31:16 GMT

Thanks.. I will try and confirm.

Re: How can I get aggregated value ?

haupenthals — Thu, 20 Sep 2018 06:54:39 GMT

Try this:

TAB:
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)
LOAD Rating,
if(wildmatch(Rating, 'A*'),'A-AAA', if(wildmatch(Rating, 'B*'), 'B-BBB', Null())) as New_Rating
Resident TAB;

Then you can create a simple table Chart: