topic Re: Mapping Fields - Aggr. Group vs. Single in QlikView

Mapping Fields - Aggr. Group vs. Single

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

usually I compare a group of companies against another single company within a sector.

In the attached example I do compare A and B as the group against H.

On this account I have created a chart with the corresponding company dimensions in the set analysis.

What I wanna do now is to examine the data within a sector on corporate group level with the same target of

comparing groups. Hence, I have aggregated the data in the load script.

My problem now is that in my original qlikview document many analysis including the set analysis on company level are implemented.

Is there any way to map or use an if clause to switch between the two levels without changing all expressions in the chart.

Like when choosing the corporate group level, this substitutes the company field.

So for the attached example, leave the chart unchanged. Now I would like the aggregated values of group 1 and as peer group 2 compared in the chart.

I hope I made myself clear.

Many thanks in advance!

Re: Mapping Fields - Aggr. Group vs. Single

kaushiknsolanki — Wed, 17 Aug 2016 14:22:21 GMT

Hi,

Sorry but I am not clear with your problem.

Can you give example of the data which you shared. What selection you are doing and what output you are expecting.

Regards,

Kaushik Solanki

Re: Mapping Fields - Aggr. Group vs. Single

Wed, 17 Aug 2016 14:39:48 GMT

Ok, when making the following selections:

Sector: Automotive

Corporate Group: Group1

Corporate Peer: Group2

I am exptecting the values of the both lower tables to be displayed in the chart without actually changing anythin in the chart.

Re: Mapping Fields - Aggr. Group vs. Single

b_garside — Wed, 17 Aug 2016 15:18:05 GMT

Sounds like your asking how to do dynamic aggregation groupings that match the drill down values. You can use the GetCurrentField(). See this posting: https://community.qlik.com/message/721703#721703

Re: Mapping Fields - Aggr. Group vs. Single

kaushiknsolanki — Wed, 17 Aug 2016 16:08:45 GMT

Hi,

Hope this is what you are looking for

Regards,

Kaushik Solanki

Re: Mapping Fields - Aggr. Group vs. Single

Thu, 18 Aug 2016 09:02:26 GMT

Unfortunately it is not, as far as I understand what you did.

I have tried to illustrate the output I expect in the above file.

Re: Mapping Fields - Aggr. Group vs. Single

Thu, 18 Aug 2016 09:14:36 GMT

Yes I think we are getting closer, even though I do not get anything in the link.

However, I have attached another example to illustrate my expected output.

Some explanations:

I have created an alternate state just for testing. I do not want to use it finally.

In the top you see the output I exptect when I want to do analytics on company level (see title of the chart).

Below you can see the output I exptect when doing analytics on any group level (see title of the chart).

In the chart "switch" I have tried to concatenate both levels by implementing conditional dimensions without changing the expressions. When choosing the single companies as in the upper part you get the same result. For the group level, only the group (in this case group1) and not the corporate peer (in this case group2) is displayed in the switch chart.

What am I missing to get group 2 displayed? The best solution for me would be not to change the expression somehow.... (The issue seems to be the "Company=p(Company_Peer)")

Re: Mapping Fields - Aggr. Group vs. Single

sunny_talwar — Thu, 18 Aug 2016 09:24:31 GMT

Can you check now

Re: Mapping Fields - Aggr. Group vs. Single

Thu, 18 Aug 2016 10:30:47 GMT

Thank you, that it is.

However, this works out on raw data but not on calculated fields. Thus, a few more questions on this:

I have a lot of KPIs calculated.

1. Is there a faster/better way of calculating those in the load script than doing it all over again?

Load
Company,
Sector,
CorporateGroup,
Year,
DataX/DataY as KPI1
Resident Data;


GroupKPIs:
Load 
Sector,
CorporateGroup,
Year,
Sum(DataX)/Sum(DataY) as KPI1_Group
Resident Data Group By Sector, CorporateGroup, Year;

2. In the attached file I have created conditional expressions to switch between KPI1 and KPI1_Group.

Is there an easier solution? Maybe substituting the string "_Group" with a variable? How could I set variable values on that conditions (GetSelectedCount(Company_Peer) <> 0)?

Re: Mapping Fields - Aggr. Group vs. Single

Thu, 18 Aug 2016 14:10:33 GMT

Ok, so I think I have found another good solution without touching any chart or expression.

I have loaded the Group as Company in the Load Script. I do have a question on this:

1. Can you apply the sum() operator for the whole "Group:" load? In my original I have more than 1000 fields in the "data" load, so I would need to do that manually for any field...

2. Why is Group 1 not displayed in the chart? I am pretty sure this is a small mistake but I cannot find it..

Many thanks!

Re: Mapping Fields - Aggr. Group vs. Single

sunny_talwar — Thu, 18 Aug 2016 14:42:51 GMT

Try this expression for your second question:

=Sum({<Company=p(Company_Peer), Company_Peer, CorporateGroup_Peer, CorporateGroup>}KPI1)

Can you point me in the script, where exactly are you hoping to make a change?

Re: Mapping Fields - Aggr. Group vs. Single

Thu, 18 Aug 2016 14:49:03 GMT

In the script this load sums all the data to get the corporate group data.

Group:
NoConcatenate
Load
CorporateGroup as Company,
Sector,
CorporateGroup,
Year,
Sum(DataX) as DataX,
Sum(DataY) as DataY,
Sum(DataZ) as DataZ,
Sum(DataA) as DataA
Resident Data Group By Sector, CorporateGroup, Year;

In my original I have like more than 1000 Data fields: Data1,Data2,...Data1000,Data1001.

Any chance to do a loop in this load for the sum() operator based on the resident table?

Re: Mapping Fields - Aggr. Group vs. Single

sunny_talwar — Thu, 18 Aug 2016 14:56:14 GMT

Why don't you use CrossTable to simplify your data and make your life easier

The Crosstable Load

Re: Mapping Fields - Aggr. Group vs. Single

Thu, 18 Aug 2016 15:00:44 GMT

My Input does already look like the output in your link or what are you after??!

Re: Mapping Fields - Aggr. Group vs. Single

sunny_talwar — Thu, 18 Aug 2016 16:12:41 GMT

Your DataX, DataY, DataZ, ... and 1000 other Data... can be converted into a single Data field with their value in another field.

Re: Mapping Fields - Aggr. Group vs. Single

Fri, 19 Aug 2016 06:30:24 GMT

Well ok, but how can that be utilized to solve the problem of the multiple sum() operators I need?

Could you provide an example?

In general, the link proposes to use the output presented. As far as I can see my input already looks like this.

Product is Company, Month is Year, Sales is DataX and so on...

Re: Mapping Fields - Aggr. Group vs. Single

sunny_talwar — Fri, 19 Aug 2016 06:33:48 GMT

I will be traveling most of the part today, but will try to come up with a sample as soon as I get some free time in between travels

Re: Mapping Fields - Aggr. Group vs. Single

Fri, 19 Aug 2016 07:35:23 GMT

That is no problem, thanks for the effort anyway!

I have made an attempt and it seems to work out using the crosstable to sum up the values by using the "group by".

Then I have reversed this part to concatenate the Corporate Group Data with the Company data as done before.

I would appreciate if you could check whether this is the solution you were proposing and if there are any enhancements! 🙂

Re: Mapping Fields - Aggr. Group vs. Single

sunny_talwar — Fri, 19 Aug 2016 07:57:05 GMT

Yes sir, this is exactly how I thought we would do this. In fact you took it one step further where you recreated your old format using the Generic Load (Not sure if you really need this part, but if you want your data to be in certain way, then this is great).

Just by quickly looking over it, I don't see anything that seems to be changed.

Great Work!!

Best,

Sunny

Re: Mapping Fields - Aggr. Group vs. Single

Fri, 19 Aug 2016 07:58:29 GMT

Thanks for your support and save travels!