All combination or pairs of a field

[educastri83]

Hi everybody,

I have to do some simple task but I don't know what to do... I need to get all combination of  a field values columns. 

For example, i have 2 tables: "Group" and "Users", something like this:

Group:

load * inline [Id, Business_Rol

1, Cashier

2, Audit

3, Warehouse

4, Accounting

5, Admin

6, Database];

Users:
load * inline [User, Business_Rol

User1, Cashier

User1, Audit

User2, Warehouse

User1, Accounting

User3, Admin

User3, Database

User4, Cashier

User4, Audit

User4, Warehouse

User4, Accounting

User4, Admin

User4, Database

User5, Accounting

User5, Warehouse];

I need a new field  that show pairs or duplas of business roles. In case of "User1" the combinations must be like:

Cashier - Audit
Cashier - Accounting
Audit - Cashier
Audit - Accounting
Accounting - Cashier
Accounting - Audit

Is it possible to achieve something like this or am I omitting some intermediate table?

Thanks 

[Saravanan_Desingh · Accepted solution]

Can you check this?

Users:
Load * Inline [
User, Business_Rol
User1, Cashier
User1, Audit
User2, Warehouse
User1, Accounting
User3, Admin
User3, Database
User4, Cashier
User4, Audit
User4, Warehouse
User4, Accounting
User4, Admin
User4, Database
User5, Accounting
User5, Warehouse];
Outer Join (Users)
LOAD User, Business_Rol As Rol2
Resident Users;

tabUsers:
NoConcatenate
LOAD User, Business_Rol&' - '&Rol2 As New_Field
Resident Users
Where Business_Rol <> Rol2;

Drop Table Users;

[educastri83]

Thanks @Saravanan_Desingh  it works!!!

[Saravanan_Desingh]

you welcome 🙂

[rajeshwar1]

can we use this to dynamically form multiple combinations of fields also?