Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Use dimension as expression

Hi

For this question i use the following example table:

TEST_TABLE:
LOAD * INLINE [
id,yr,course,ind1, ind2,prov
001,2017,a,0,0,10
001,2017,a,1,0,11
001,2018,b,0,1,12
001,2019,c,0,1,13
002,2018,a,1,0,20
002,2018,a,0,0,21
002,2019,b,0,1,22
002,2020,c,0,1,23
];

Where id is an identification number, yr is year, course is a course code, ind1 and ind2 are [0,1]-indicators indicating which course was completed for level 1 and 2, and prov is the course provider. 

The output i want is a table with id in rows, yr in colomns, where the cells are populated with the course  code completed.

I use a pivot table/chart. In the attached picture there two tables, where I for simplicity have yr as rows instead of colomns. There you also can see the expressions I have written. In the first table the correct course provider and course code is listed. Then i want to use the course dimension as expression. In the second table I have tried to solve this, but course code a is not listed.

By reading posts in the Qlik-community, it seems like this can be solved by aggr. I have used aggr in several other expressions, but I have not been able to figure it out this time.

I hope someone can help me!

Regards,

Torbjørn

 

Labels (1)
1 Solution

Accepted Solutions
Highlighted

I think you can get away without using aggr()...

If you set your chart up like this:
Dimension 1: id
Dimension 2: yr
Expression: only({<ind1={1}>+<ind2={1}>} course)

You end up with a table that looks like this:
screenshot.png

 

 

 

If there is any possibility of having more than one course in a year, you may want to change your expression to:
concat({<ind1={1}>+<ind2={1}>} course, ', ')

View solution in original post

2 Replies
Highlighted

I think you can get away without using aggr()...

If you set your chart up like this:
Dimension 1: id
Dimension 2: yr
Expression: only({<ind1={1}>+<ind2={1}>} course)

You end up with a table that looks like this:
screenshot.png

 

 

 

If there is any possibility of having more than one course in a year, you may want to change your expression to:
concat({<ind1={1}>+<ind2={1}>} course, ', ')

View solution in original post

Highlighted
Contributor III
Contributor III

Thank you! Your solution works perfectly! Have a nice day 🙂

T