topic Re: Only display unique user, mutiple roles, only display 1 in QlikView

Only display unique user, mutiple roles, only display 1

pho3nix90 — Fri, 02 Mar 2012 12:34:51 GMT

Hi Guys, need some help.

I have the following sample table:

ID	NAME	ROLE
1	User 1	Role 1
1	User 1	Role 2
2	User 2	Role 4
2	User 2	Role 3
3	User 3	Role 3
3	User 3	Role 4
4	User 4	Role 4

So basically, I want to display unique users, and if they belong to role 1 & 2, only display role 2, and If they have role 2 & 3, only display role 2, and the rest display normal.

Only display unique user, mutiple roles, only display 1

CELAMBARASAN — Fri, 02 Mar 2012 12:39:33 GMT

Hi,

Try with this

SampleTable:

Load

ID,

NAME,

ROLE

From ...

Output:

Load

ID as NewID,

NAME as NewName,

LastValue(ROLE) as Role

Resident

SampleTable GROUP BY ID,NAME;

Drop Table SampleTable;

Celambarasan

Re: Only display unique user, mutiple roles, only display 1

jagan — Fri, 02 Mar 2012 12:39:45 GMT

Hi,

Check the attached file, hope it helps you.

Regards,

Jagan.

Re: Only display unique user, mutiple roles, only display 1

pho3nix90 — Fri, 02 Mar 2012 12:47:29 GMT

HI,

Let me try to explain again:

If user has more than 1 role, then if role buyer and requestor, then only use buyer BUT

if user has more than 1 role, then if role manager and dept managerm then only show dept manager.

USER ROLE

John Doe Buyer

John Doe Requestor

Jane Doe Dept Manager

Jane Doe Manager

Mark Requestor

Sally Buyer

So basically it will then return:

USER ROLE

John Doe buyer

Jane Doe dept manager

Mark requestor

sally buyer

Re: Only display unique user, mutiple roles, only display 1

pho3nix90 — Fri, 02 Mar 2012 12:52:57 GMT

Celambarasan Adhimulam wrote:
Hi,
     Try with this
     SampleTable:
     Load
          ID,
          NAME,
          ROLE
     From ...
     Output:
     Load
          ID as NewID,
          NAME as NewName,
          LastValue(ROLE) as Role
     Resident
     SampleTable GROUP BY ID,NAME;
Drop Table SampleTable;
Celambarasan

This wont work because the roles aren't in a specific order.

Only display unique user, mutiple roles, only display 1

CELAMBARASAN — Fri, 02 Mar 2012 12:55:24 GMT

Hi,

Try with this

Load

USER,

Pick(WildMatch(Concat(ROLE),'*Buyer*Requestor*','*Dept Manager*Manager*','*'),'Buyer','Dept Manager') as Role

Resident SampleTable;

Celambarasan

Re: Only display unique user, mutiple roles, only display 1

pho3nix90 — Fri, 02 Mar 2012 13:01:42 GMT

Celambarasan Adhimulam wrote:
Hi,
     Try with this
     Load
          USER,
          Pick(WildMatch(Concat(ROLE),'*Buyer*Requestor*','*Dept Manager*Manager*','*'),'Buyer','Dept Manager') as Role
     Resident SampleTable;
Celambarasan

HI,

What about the other users that only have 1 dept? they should be touched nor should their departments be changed, but will try now.

Re: Only display unique user, mutiple roles, only display 1

pho3nix90 — Fri, 02 Mar 2012 13:07:41 GMT

It's giving me invalid expression.

Attached is a qvw sample of what the data (sampel) is and what I require.