Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Merge dims on multiple rows on single row in straight table

Hi,

I am having a bit of trouble merging multiple attributes linked to a fact table into a single row. Any help would be greatly appreciated  

Here is the data model.

dm.png

There are multiple roles/people associated with every project. Some of these people are internal users and others are external users.

This is the desired o/p

op.png

For every project we want to merge the multiple roles into a single row in a straight table.

I tried playing around with  Aggr() function e.g. Aggr(MaxString({<ROLE={'Client'}>} ORG_NAME), ORG_NAME)

but it doesn't work when all the fields are added. I am definitely missing something but don't know how to proceed.

The sample app is attached. Can someone please suggest a possible approach?

Thanks in advance!

3 Replies
ogster1974
Not applicable

Re: Merge dims on multiple rows on single row in straight table

Have you tried pivoting on your data on role and bringing back your people in a separate table.  Your model supports multiple users in any given role.

Regards

Andy

Not applicable

Re: Merge dims on multiple rows on single row in straight table

Thank Andy.

That is an option but we are building a mashup which has an export functionality; So straight table is the only option.

I did get it to work by using Only()

Only({1<PROJECT_ID=P({1<PROJECT_ID=P({$}PROJECT_ID)>}PROJECT_ID), ROLE={'Project Manager'}>} USER_NAME)

and then using he same approach for other roles. !

But then, we loose the search option because we have to add it as a measure. May be I have to duplicate the users table at least into Client Contacts & Internal users.

chooco_co
Not applicable

Re: Merge dims on multiple rows on single row in straight table

Hi Aravind

Is that visualization that you want fixed column?

I tried using pivot to produce that you want. see attached file

1.png