Permutations on qlik sense(not just calculate the number, but create all the concatenated possibilities)

[edupitel7777]

Hi Guys
I'm trying to achieve  combinations and permutations on Qlik sense
like C 10, 8 = 45 rows
it is from 10 elements how many combinations you get if choose 8  without repetiions =  10!/8!/2! = 45 combinations
my code is generating all the combinations  

BUT
Qlik is doing  8 joins in a loop
and this force qlik to   20Milions + rows before compute, filter and show the 45 final result rows

Are there a better approuch to achieve this without this massive number of rows ?

ex: Brazilian most famous lotterys as example are choose 6 between 60  (50M + combinations) and choose 15 between 25 (3,2M +)  combinations

it would be impossible just with my actual code, it spends 1minute just to compute choose 8 between 10 lol 

Follow the code Below:

Origin:
LOAD RowNo() as Numbers
AutoGenerate(10);

Count:
LOAD Count(Numbers) as CountNumbers
Resident Origin;

LET lr = Peek('CountNumbers');
DROP Table Count;

FOR i = 1 to 8//$(lr)-2
If i = 1 Then
Set tab = 'Table:';
ELSE
Set tab = 'Join (Table)';
ENDIF

$(tab)

LOAD Numbers as [Numbers-$(i)] resident Origin;

CROSS:
CROSSTABLE (Header, Name)
LOAD recno() as ID, *
RESIDENT Table;

TMP:
LOAD ID as IDValid
WHERE Max =1;

LOAD ID,max(Count) as Max
GROUP BY ID;

LOAD ID, Name, Count(Name) as Count
Resident CROSS
GROUP BY ID, Name;

TMPTABLE:
NOCONCATENATE
LOAD * Resident Table
WHERE EXISTS(IDValid,recno());

DROP TABLE TMP, Table;
Rename Table TMPTABLE to Table;
NEXT

CROSS:
CROSSTABLE (Header, Name)
LOAD recno() as ID, *
RESIDENT Table;

RESULT:
LOAD Permut
where count=1;

LOAD Permut,count(distinct if(not isnull(subfield(Permut,'-',8)) and isnull(subfield(Permut, '-',9)), Permut)) as count
Group by Permut;

LOAD CONCAT(distinct Name, '-', num#(Header) ) as Permut
Resident CROSS
GROUP BY ID;

DROP TABLE Origin, CROSS,Table;

exit script;

[SerhanKaraer · Accepted solution]

Hello edupitel,

You can find a subroutine-like script below.

You can adjust vPossibleNumbers and q variables' values and get combinations as a table.

For your comb(60,6) parameters, it generates results approximately in 3 minutes.

SET vPossibleNumbers=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60;
LET p = SubStringCount('$(vPossibleNumbers)',',')+1;
LET q = 6;

SET vLoadStatement=LOAD SubField('$(vPossibleNumbers)',',',IterNo()) as n1 AutoGenerate 1 while IterNo() <= $(p) - $(q) + 1==semicolon==;

For i = 1 to $(q) - 1
	for nf = 1 to $(i)
  		LET numberFields = if($(nf) = 1, 'n1', '$(numberFields), n$(nf)');
  	next nf
	LET j = $(i) + 1;
	LET vPrefix = if($(i) = $(q) - 1, '', 'LOAD ');
	SET vLoadStatement=$(vPrefix)$(numberFields), SubField(mid('$(vPossibleNumbers)', index('$(vPossibleNumbers)',n$(i)&',') + len(n$(i)&',')),',',IterNo()) as n$(j) while IterNo() <= $(p) - $(q) + 1 - (n$(i) - $(i))==semicolon==
	$(vLoadStatement);
next i

LET vLoadStatement = Replace('$(vLoadStatement)','==semicolon==',chr(59));

Combinations:
LOAD $(vLoadStatement);

 I hope it solves your problem.

[edupitel7777]

LOL you are such a Genious!   thanks man
I will study your code, could you please give a little explanation about it, I miss the shot in the middle of the way, subfield  and substringcount are hard to understand for me. 

I will submit a new question if it is needed:

how can I create many filters and apply them simultaneously:
ex : odd numbers, primes, end-0 , first columns, fibonacci sequence
and every "card"  with  6 numbers for example will be "classified in these 10-50 filters"
card 1                                           filter1     filter2  filter3   filter4  ...                         filter30
12-28-32-49-54 -60                      4           3           2                8                                        6
showing the number os matches beetween the filter and the "card" 

Like a school test with  100 questions and we have  100 fields showing Y/N  for each student ;

This is harder, I don't know if Qlik is capable of such thing

In the Load script I will set  vmin and vmax for each filter
or  other uses

Create all combinations of 8 numers in 60  for example
see each filter as "one of the last 100 drawn"
and study the analitics of each combination in the last 100 drawn

this combination of ID xxx  made y points in the last 10 drawns   (many analysis opportunities)  

For better clarification  :  A =1-2-3-4-5-6    B=4-6-9-10-12-13    how many matches I have between A and B
and if I have 100 other "filters" to compare A with C  , A with D, A with E , A with F , A with G ...

how can I proceed

[SerhanKaraer]

Hi,

The script generates a dynamic preceding load statements and does all the calculations in one block.

It starts with n1. n1 can take up values from 1,2,3,....54,55. If it is 56, then the rest of the numbers can not take values up to 60 as there are not enough numbers to pick for n2, n3, n4, n5 and n6.

If n1 takes 1, then n2 can take values from 2 to 56. If n1 takes 55, then n2 can only be 56.

Similarly, if n5 is 50, then n6 can take up values from 51 to 60.

Thus, the possibility for the following number depends on just the previous numbers' value as the numbers are in increasing order. 

Here is the generated script for combination of 6 from 60.

LOAD n1, n2, n3, n4, n5, SubField(mid('$(vPossibleNumbers)',n5&',') + len(n5&',')),',',IterNo()) as n6 while IterNo() <= 60 - 6 + 1 - (n5 - 5);
LOAD n1, n2, n3, n4, SubField(mid('$(vPossibleNumbers)',n4&',') + len(n4&',')),',',IterNo()) as n5 while IterNo() <= 60 - 6 + 1 - (n4 - 4);
LOAD n1, n2, n3, SubField(mid('$(vPossibleNumbers)',n3&',') + len(n3&',')),',',IterNo()) as n4 while IterNo() <= 60 - 6 + 1 - (n3 - 3);
LOAD n1, n2, SubField(mid('$(vPossibleNumbers)',n2&',') + len(n2&',')),',',IterNo()) as n3 while IterNo() <= 60 - 6 + 1 - (n2 - 2);
LOAD n1, SubField(mid('$(vPossibleNumbers)',n1&',') + len(n1&',')),',',IterNo()) as n2 while IterNo() <= 60 - 6 + 1 - (n1 - 1);
LOAD SubField('$(vPossibleNumbers)',',',IterNo()) as n1 AutoGenerate 1 while IterNo() <= 60 - 6 + 1;

 For substringcount and subfield, you should check help site:

https://help.qlik.com/tr-TR/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Scripting/StringFunctions...

https://help.qlik.com/tr-TR/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Scripting/StringFunctions...

Subfield function in the above script gives values to the corresponding number that it can take one by one by the help of while loop and iterno. For instance, if n2 can take values from 5 to 56, it generates a new record with each value. Be careful for the first parameter in the subfield function, it is dependent of the previous numbers' value, a dynamic string.

Substringcount in the above script returns the number of possible values provided for calculating the combination by counting the number of commas.

For the filter part, since there is no proper way to define custom function in qlik script. Either you will have to create a subroutine for each filter, or a variable working with parameters.

However, you can create your custom functions with analytical connection, but it needs a good expertise.

https://help.qlik.com/en-US/sense-developer/May2022/Content/Sense_Helpsites/AnalyticConnections.htm

Therefore, I guess doing the work with a programming language will be easier.  

Good luck with that.

[edupitel7777]

Hi SerhanKaraer  thanks again for your help I really appreciate that

Regarding the "filter part"  I want the equivalent to the excel formula :
sumproduct(--isnumber(match(A2:A10;B2:B10;0)))
when I have 2 lists with concatenated values  1-4-50-20-ABC-31-TT-44-88     and 1-20-31-22-44-55
and I want to count the matches between the 2 lists (1-20-31-44) = 4 matches in this case

Your previous reply
"Either you will have to create a subroutine for each filter, or a variable working with parameters".

This can be achieved in Qlik sense ?
I really need to create a sub-routine for each filter or there are a better way to approach this problem?
Could you write a piece of code to achieve the same excel formula result?

I'll exercise better my "subfield" skills

I'm sending 3 prints from a LotoFacil software (one of the most famous lotto's in Brazil) with some filters that are nothing more than   concatenated sequence of numbers

[SerhanKaraer]

For your match count of two listes, here are two sample subs.

sub countMatchValues(first, second)

let id = AutoNumberHash128('$(first)','$(second)');

"countMatchValues_$(id)":
NoConcatenate
load number where count > 1;
load number, count(1) as count group by number;
load subfield(numbersconcat,'-') as number;
load * inline [
numbersconcat
$(first)
$(second)
];

let result = NoOfRows('countMatchValues_$(id)');

drop table "countMatchValues_$(id)";

end sub

call countMatchValues('1-4-50-20-ABC-31-TT-44-88','1-20-31-22-44-55');
comparisonTable:
load '1-4-50-20-ABC-31-TT-44-88' as first, '1-20-31-22-44-55' as second, '$(result)' as matchCount AutoGenerate 1;

call countMatchValues('1-2-3-4-5-6','2-4-6-8-10-12');
load '1-2-3-4-5-6' as first, '2-4-6-8-10-12' as second, '$(result)' as matchCount AutoGenerate 1;

sub countMatchTables(inputTable)

let firstField = FieldName(1,'$(inputTable)');
let secondField = FieldName(2,'$(inputTable)');

join ('$(inputTable)')
  load subfield(id, ';', 1) as $(firstField), subfield(id, ';', 2) as "$(secondField)", matchCount;
  load id, count(numbers) as matchCount where count > 1 group by id;
  load id, numbers, count(1) as count group by id, numbers;
  load id, subfield("$(firstField)"&'-'&"$(secondField)",'-') as numbers;
  load "$(firstField)" & ';' & "$(secondField)" as id, "$(firstField)", "$(secondField)" Resident "$(inputTable)";

let firstField;
let secondField;

end sub

LotteryResult:
load * inline [
first, second
1-4-50-20-ABC-31-TT-44-88, 1-20-31-22-44-55
1-2-3-4-5-6, 2-4-6-8-10-12
1-2-3-4-5-6, 5-6-7-8-9-10-11
];

call countMatchTables('LotteryResult');

First one will be slower when comparison volume is high.

As I mentioned, you can have hard times when applying all your filters. You are limited to the capabilities of functions in Qlik script, whereas you are much free with a programming language.