topic Re: Qlik Sense Permutations in App Development

Qlik Sense Permutations

Karjala — Wed, 14 Jan 2026 19:02:10 GMT

I have this table with Name and Color

ColorData:

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

ColorDataCombos:

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

Re: Qlik Sense Permutations

Daniel_Castella — Thu, 15 Jan 2026 11:27:25 GMT

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

Re: Qlik Sense Permutations

BIAKS — Thu, 15 Jan 2026 15:03:52 GMT

@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;

Re: Qlik Sense Permutations

Daniel_Castella — Thu, 15 Jan 2026 16:31:39 GMT

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

Re: Qlik Sense Permutations

BIAKS — Fri, 16 Jan 2026 06:37:21 GMT

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

Re: Qlik Sense Permutations

Daniel_Castella — Fri, 16 Jan 2026 12:44:29 GMT

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

Re: Qlik Sense Permutations

BIAKS — Fri, 16 Jan 2026 14:32:03 GMT

@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 🙂

Re: Qlik Sense Permutations

MarcoWedel — Sat, 17 Jan 2026 12:18:15 GMT

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