Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
educastri83
Creator
Creator

All combination or pairs of a field

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

newfield.png

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

Thanks 

Labels (4)
1 Solution

Accepted Solutions
Saravanan_Desingh

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;

commQV17.PNG

View solution in original post

4 Replies
Saravanan_Desingh

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;

commQV17.PNG

educastri83
Creator
Creator
Author

Thanks @Saravanan_Desingh  it works!!!

Saravanan_Desingh

you welcome 🙂

rajeshwar1
Partner - Contributor III
Partner - Contributor III

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