Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mfarsln
Creator II
Creator II

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?

Labels (3)
1 Solution

Accepted Solutions
rogerpegler
Creator II
Creator II

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.

View solution in original post

4 Replies
rogerpegler
Creator II
Creator II

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
Creator II
Creator II
Author

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
Creator II
Creator II

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
Creator II
Creator II
Author

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