Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
a-5
Contributor III
Contributor III

Create a new field including all possible combinations of two columns

Hi! 

I have two fields (Field1 & Field2) and I'd need to create a new field (Result) including all possible combinations of values in Field1 & Field2:

Field1 Field2
1 A
2 B
3 C

 

Result
1-A
2-A
3-A
1-B
2-B
3-B
1-C
2-C
3-C

 

Any idea how to create Result field?

Thank you so much!

Labels (1)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

Try something like this :

CartesianProduct:

LOAD Field1 FROM Source;

JOIN (CartesianProduct) Field2 FROM Source;

Final:

LOAD Field1, Field2, Field1 &' - '&Field2 as Result

RESIDENT CartesianProduct;

DROP TABLE CartesianProduct;

View solution in original post

2 Replies
sabeeh
Contributor II
Contributor II

Creating all possible combinations can generate a large number of rows, impacting performance and usabilityConsider alternative approaches based on the number of combinations and desired functionality.

It's important to provide more context about your dataset size, the number of unique values in each field, and your desired use case for the "Result" field to suggest the most suitable approach. Feel free to share more details for further assistance.

Vegar
MVP
MVP

Try something like this :

CartesianProduct:

LOAD Field1 FROM Source;

JOIN (CartesianProduct) Field2 FROM Source;

Final:

LOAD Field1, Field2, Field1 &' - '&Field2 as Result

RESIDENT CartesianProduct;

DROP TABLE CartesianProduct;