Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
fheidenstecker
Partner - Creator II
Partner - Creator II

Permutations without repeat

Hi There,

i have 3 values and i want to combine these 3 without repeat

The 3 values are John, George and Paul (sorry Ringo 😉 )

That is the result I want to have

                    

John    

George    

Paul    

John    George

John    Paul

Paul    George

John    George    Paul

What algorithm could create this result? I tried karthesian product, but this causes repeats , for example

John Goerge

George John

and so on.

I really would appriciate a hint!

Thanks,

Fabian

1 Solution

Accepted Solutions
sunny_talwar

May be this

Table:

LOAD * INLINE [

    Name1

    John

    George

    Paul

];


Left Join (Table)

LOAD Name1 as Name2

Resident Table;


Left Join (Table)

LOAD Name1 as Name3

Resident Table;


TempTable:

CrossTable (Header, Name)

LOAD RowNo() as RowNum,

Name1,

Name2,

Name3

Resident Table;


FinalTable:

LOAD Distinct Name;

LOAD RowNum,

Concat(DISTINCT Name, ',') as Name

Resident TempTable

Group By RowNum;


DROP Table Table, TempTable;

Capture.PNG

View solution in original post

3 Replies
sunny_talwar

May be this

Table:

LOAD * INLINE [

    Name1

    John

    George

    Paul

];


Left Join (Table)

LOAD Name1 as Name2

Resident Table;


Left Join (Table)

LOAD Name1 as Name3

Resident Table;


TempTable:

CrossTable (Header, Name)

LOAD RowNo() as RowNum,

Name1,

Name2,

Name3

Resident Table;


FinalTable:

LOAD Distinct Name;

LOAD RowNum,

Concat(DISTINCT Name, ',') as Name

Resident TempTable

Group By RowNum;


DROP Table Table, TempTable;

Capture.PNG

fheidenstecker
Partner - Creator II
Partner - Creator II
Author

What a clever use of crosstable!

Thanks stalwar1

sunny_talwar

Hahaha yes, I am glad we were able to figure it out using CrossTable()