Only display unique user, mutiple roles, only display 1

[pho3nix90]

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.

[CELAMBARASAN]

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

[jagan]

Hi,

Check the attached file, hope it helps you.

Regards,

Jagan.

[pho3nix90]

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

[pho3nix90]

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.

[CELAMBARASAN]

Hi,

     Try with this

     Load

          USER,

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

     Resident SampleTable;

Celambarasan

[pho3nix90]

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.

[pho3nix90]

It's giving me invalid expression.

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