Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
mfarsln
New Contributor III

Adding dimension values to already loaded dimension

Hi!

I need to create a table which simply looks like this:

Ekran Alıntısı.PNG

In this table, i already have a dimension which comes from sql query and measures as a, b and c. Think this dimension as company brands, i have 100-110 distinct brand values. 

What i want to do is to show averages, averages of specific brand groups and something like these. I know how to calculate it by using set analysis but what i couldn't achieve yet is that creating custom dimension values and adding them to already loaded dimension, not as a new dimension. Is it possible to be done in qlik sense?

1 Solution

Accepted Solutions
rogerpegler
Contributor

Re: Adding dimension values to already loaded dimension

In the load script:

 

Concatenate (Table)

Load 'Avg' as Field,

sum(a)/count(a) as a,

sum(b)/count(b) as b,

sum(c)/count(c) as c

Resident Table;

 

That will create the "Avg" record. You can repeat for the other records depending on what the actual logic needed.

4 Replies
rogerpegler
Contributor

Re: Adding dimension values to already loaded dimension

In the load script:

 

Concatenate (Table)

Load 'Avg' as Field,

sum(a)/count(a) as a,

sum(b)/count(b) as b,

sum(c)/count(c) as c

Resident Table;

 

That will create the "Avg" record. You can repeat for the other records depending on what the actual logic needed.

mfarsln
New Contributor III

Re: Adding dimension values to already loaded dimension

Thank you for the answer. It helped me a lot. But now i am facing with another problem. I need to use these concatenated rows as yearly analysis. Since we can't use set analysis in script i am not able to tell to qlik to get averages of max(year)-1. Is there a way to use set analysis logic inside the script?
rogerpegler
Contributor

Re: Adding dimension values to already loaded dimension

Assuming Year is in the table, the load below will calculate an average per year which you can then reference in set analysis.  If Year is not in the table then it becomes a data model question - you may need to add in a field that represents year but doesn't conflict with where year is stored for general use eg a calendar table.

Also please not that these 'group by' clauses are slow to process and use should be minimised.

Concatenate (Table)

Load 'Avg' as Field,

Year

sum(a)/count(a) as a,

sum(b)/count(b) as b,

sum(c)/count(c) as c

Resident Table

Group by Year;

mfarsln
New Contributor III

Re: Adding dimension values to already loaded dimension

Yes, year is in the table. I will try this.
Thank you for the answers.