Qlik Sense Permutations

[Karjala]

I have this table with Name and Color

ColorData:

LOAD * INLINE [

Name, Color

Bob, Red

Bob, Yellow

John, Blue

John, Red

John, Yellow

Linda, Blue

Linda, Green

Linda, Yellow ];

I want to get all the distinct sorted combinations of each name. Joined by a pipe | symbol.

ColorDataCombos:

LOAD * INLINE [

Name, ColorCombo

Bob, Red|Yellow

John, Blue|Red

John, Blue|Yellow

John, Red|Yellow

John, Blue|Red|Yellow

Linda, Blue|Green

Linda, Blue|Yellow

Linda, Green|Yellow

Linda, Blue|Green|Yellow ];

Note: if it is not possible to get sorted and distinct ones that’s fine. I’ll take all permutations for each name even though there may be many

[Daniel_Castella]

Hi @Karjala 

Try this code, in my end the results seem correct. Also, with the counter I set, it is scalable if the users have more than 3 different colors:

ColorData:

LOAD * INLINE [

Name, Color

Bob, Red

Bob, Yellow

John, Blue

John, Red

John, Yellow

Linda, Blue

Linda, Green

Linda, Yellow

];

Counter:

LOAD Name, 

Count(Name) as CounterName

RESIDENT ColorData

GROUP BY Name;

Counter2:

Load Max(CounterName) as CounterMax

RESIDENT Counter;

LET vCounter = peek('CounterMax',0,'Counter2');

ColorCombo:

NoConcatenate

LOAD Name,

Concat(Color,'|') as ColorCombo

RESIDENT ColorData

GROUP BY Name;

DROP TABLE ColorData, Counter, Counter2;

FOR i = 1 TO $(vCounter)-1

Concatenate(ColorCombo)

LOAD Name,

     Replace(ColorCombo, SubField(ColorCombo, '|', $(i)) & '|', '') AS ColorCombo

RESIDENT ColorCombo;

NEXT i;

Concatenate(ColorCombo)

LOAD Name,

     Left(ColorCombo, FindOneOf(ColorCombo, '|', -1) - 1) AS ColorCombo

RESIDENT ColorCombo;

ColorCombo2:

NoConcatenate

LOAD Distinct Name, ColorCombo

RESIDENT ColorCombo

WHERE ColorCombo like '*|*';

DROP TABLE ColorCombo;

Let me know if it works for you.

Kind Regards

Daniel

[BIAKS]

@Daniel_Castella your code works fine but it doesnt work for 4+ colors.

I slightly modified it to work with 4+ colors:

ColorData:
LOAD * INLINE [
Name, Color
Bob, Red
Bob, Yellow
John, Blue
John, Red
John, Yellow
Linda, Blue
Linda, Green
Linda, Yellow
Linda, Red
Linda, Black
Linda, White
Linda, Purple
];

Counter:
LOAD 
	Name,
	Count(Name) as CounterName
RESIDENT ColorData
GROUP BY Name;

Counter2:
Load Max(CounterName) as CounterMax
RESIDENT Counter;
 
LET vCounter = peek('CounterMax',0,'Counter2');

ColorCombo:
NoConcatenate
LOAD 
	Name,
	Concat(Color,'|') as ColorCombo
RESIDENT ColorData
GROUP BY Name;
DROP TABLE ColorData;

FOR i = 1 TO $(vCounter)-1
	Concatenate(ColorCombo)
	LOAD distinct
		Name,
	    Replace(ColorCombo, SubField(ColorCombo, '|', $(i)) & '|', '') AS ColorCombo
	RESIDENT ColorCombo;
    
    Concatenate(ColorCombo)
	LOAD distinct
		Name,
	    Replace(ColorCombo, '|' & SubField(ColorCombo, '|', $(i)) & '|' , '|') AS ColorCombo
	RESIDENT ColorCombo;
    
    Concatenate(ColorCombo)
	LOAD distinct
		Name,
	    Replace(ColorCombo, '|' & SubField(ColorCombo, '|', $(i)) , '') AS ColorCombo
	RESIDENT ColorCombo;
NEXT i;

Concatenate(ColorCombo)
LOAD 
	Name,
    Left(ColorCombo, FindOneOf(ColorCombo, '|', -1) - 1) AS ColorCombo
RESIDENT ColorCombo;

Concatenate(ColorCombo)
LOAD
	Name,
    Replace(ColorCombo, Mid(ColorCombo, FindOneOf(ColorCombo, '|', 1)+1, FindOneOf(ColorCombo, '|', -1)-FindOneOf(ColorCombo, '|', 1)),'') as ColorCombo
Resident ColorCombo;

Concatenate(ColorCombo)
LOAD 
	Name,
    Right(ColorCombo, Len(ColorCombo) - FindOneOf(ColorCombo, '|', 1)) AS ColorCombo
RESIDENT ColorCombo;

ColorCombo2:
NoConcatenate
LOAD Distinct 
	Name, 
    ColorCombo
RESIDENT ColorCombo
WHERE ColorCombo like '*|*';
DROP TABLE ColorCombo;

[Daniel_Castella]

Hi @BIAKS @Karjala 

Yes, my bad. My previous code didn't work fine for more colors. However, I think your solution only works for 4 colors and, if you put more, there are still issues. I'm trying to get a "universal" code to work with N colors. I have created this other code that seems to work with more than 4. I have not validated all the numbers, but with 5 and 6 seems to work (and obviously for 4, 3, 2 and 1).

ColorData:

LOAD * INLINE [

Name, Color

Bob, Red

Bob, Yellow

John, Blue

John, Red

John, Yellow

Linda, Blue

Linda, Green

Linda, Yellow

Linda, Red

Linda, Brown

Linda, Pink

];

Counter:

LOAD Name, 

Count(Name) as CounterName

RESIDENT ColorData

GROUP BY Name;

Counter2:

LOAD Max(CounterName) as CounterMax

RESIDENT Counter;

LET vCounter = peek('CounterMax',0,'Counter2');

ColorCombo:

NoConcatenate

LOAD Name,

Concat(Color,'|') as ColorCombo

RESIDENT ColorData

GROUP BY Name;

DROP TABLE ColorData, Counter, Counter2;

FOR i = 1 TO $(vCounter)-1

FOR j = 0 TO ceil($(vCounter)/4)

Concatenate(ColorCombo)

LOAD Name,

     Replace(ColorCombo, SubField(ColorCombo, '|', $(i)-$(j)) & '|', '')

      AS ColorCombo

RESIDENT ColorCombo;

NEXT j;

NEXT i;

Concatenate(ColorCombo)

LOAD Name,

     Left(ColorCombo, FindOneOf(ColorCombo, '|', -1) - 1) AS ColorCombo

RESIDENT ColorCombo;

ColorCombo2:

NoConcatenate

LOAD Distinct Name, ColorCombo

RESIDENT ColorCombo

WHERE ColorCombo like '*|*';

DROP TABLE ColorCombo;

I hope it can work for more colors too, but checking all the combinations for big numbers takes a lot of time.

Kind Regards

Daniel

[BIAKS]

@Daniel_Castella why do you divide by 4 in the second for loop? I couldn't understand. Can you please explain?
Thanks.

[Daniel_Castella]

Hi @BIAKS @Karjala 

Ok, I'm dumb. I observed that, if you have 1 to 4 colors, you need to iterate with this:

Replace(ColorCombo, SubField(ColorCombo, '|', $(i)) & '|', '')

Replace(ColorCombo, SubField(ColorCombo, '|', $(i)-1) & '|', '')

However, this stop working fine with 5 colors. Then, you need an extra iteration:

Replace(ColorCombo, SubField(ColorCombo, '|', $(i)-2) & '|', '')

For this reason, every 4 colors I added an extra iteration. The ceil and the division ensure that j=1 when 1 to 4 colors. j=2 when 5 to 8 colors... etc. 

However, I just noticed I can rewrite it like this:

ColorData:

LOAD * INLINE [

Name, Color

Bob, Red

Bob, Yellow

John, Blue

John, Red

John, Yellow

Linda, Blue

Linda, Green

Linda, Yellow

Linda, Red

Linda, Brown

Linda, Pink

];

Counter:

LOAD Name, 

Count(Name) as CounterName

RESIDENT ColorData

GROUP BY Name;

Counter2:

LOAD Max(CounterName) as CounterMax

RESIDENT Counter;

LET vCounter = peek('CounterMax',0,'Counter2');

ColorCombo:

NoConcatenate

LOAD Name,

Concat(Color,'|') as ColorCombo

RESIDENT ColorData

GROUP BY Name;

DROP TABLE ColorData, Counter, Counter2;

FOR i = 1 TO $(vCounter)-1

Concatenate(ColorCombo)

LOAD Name,

     Replace(ColorCombo, SubField(ColorCombo, '|', $(vCounter)-$(i)) & '|', '')

      AS ColorCombo

RESIDENT ColorCombo;

NEXT i;

Concatenate(ColorCombo)

LOAD Name,

     Left(ColorCombo, FindOneOf(ColorCombo, '|', -1) - 1) AS ColorCombo

RESIDENT ColorCombo;

ColorCombo2:

NoConcatenate

LOAD Distinct Name, ColorCombo

RESIDENT ColorCombo

WHERE ColorCombo like '*|*';

DROP TABLE ColorCombo;

This is cleaner, only one loop and I think now it is universal, for every N colors. At least, I validated until 6 colors.

Kind Regards

Daniel

[BIAKS]

@Daniel_Castella  thanks for clarifying. You can also use distinct in the loop to remove unnecessary rows. My code is updated and I tested it to 12 colors, it works fine and really fast but your code is cleaner 🙂

[MarcoWedel]

Hi,

another solution could be:

ColorData:
LOAD * INLINE [
Name, Color
Alice, Beige
Alice, Black
Alice, Blue
Alice, Brown
Alice, Green
Alice, Grey
Alice, Orange
Alice, Pink
Alice, Purple
Alice, Red
Alice, Violet
Alice, White
Alice, Yellow
Bob,   Red
Bob,   Yellow
John,  Blue
John,  Red
John,  Yellow
Linda, Blue
Linda, Green
Linda, Yellow
Linda, Red
Linda, Brown
Linda, Pink
];


mapNameColorPos:
Mapping
LOAD Name&'|'&AutoNumber(Color,Name),
     Color
Resident ColorData
Order By Name, Color;


tabCombinations:
LOAD Name,
     Combination,
     Concat(ApplyMap('mapNameColorPos',Name&'|'&Position),'|') as ColorCombo
Where Combination bitand pow(2,Position-1) and Combination <> pow(2,Position-1)
Group By Name, Combination;
LOAD Name,
     Combination,
     IterNo() as Position
While IterNo() <= ColorCount;
LOAD Name,
     ColorCount,
     IterNo() as Combination
While IterNo() < pow(2,ColorCount);     
LOAD Name,
     Count(Color) as ColorCount
Resident ColorData
Group By Name;

hope this helps

Marco