Mapping Fields - Aggr. Group vs. Single

[#1]

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!

[sunny_talwar]

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?

[#3]

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?

[sunny_talwar]

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

The Crosstable Load

[#5]

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

[sunny_talwar]

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

[#7]

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

[sunny_talwar]

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

[#9 · Accepted solution]

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! 🙂

[sunny_talwar]

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

[#11]

Thanks for your support and save travels!