topic Re: Combinations of numbers in 1 column in App Development

Combinations of numbers in 1 column

LeeSmithBtn — Wed, 22 Dec 2021 17:20:13 GMT

Hi All,

a complex question from me that may well have been answered somewhere before but thought i would ask in case it hasnt...

what i am trying to do is work out all number permutations of 4 columns of numbers (or a single column if all concatenated together).. this could end up as 10 number columns

i have 4 columns and want to work out all combinations of number permutations for example:

col1 . col2 col3 col4

1 1 7 10

1 2 5 15

1 3 6 15

1 4 8 10

2 number combos:

1,1 : 1,2: 1,3: 1,4 : 1,7 : 1,5 : 1,6 : 1,8 : 2,5 : 3,6 : 4,8: 1,10 : 7,10 : 4,10 : 8,10 : 1,15 : 2,15 : 5,15 : 3,15 : so on and so on...

then 3 number combos:

1,1,7 : 1,7,10 : 1,2,5 : 2,5,15 : 1,5,15 : 3,6,15 : 1,6,15 : so on and so on

then 4 number combos (in this case the unique 4 columns:

1,1,7,10 : 1,2,5,15 : 1,3,6,15 : 1,4,8,10 : so on and so on...

then if i had 5 digit columns this would then go 5,6,7 number combos and so on...

i ideally then wanna take all permutations of 2digit numbers and create a 2 number combo table, a 3 number combo table, a 4 number combo table that all link based on some random generated key that combines all together

this may not be explained very well but basically wanting to work out any combinations of these numbers

Re: Combinations of numbers in 1 column

y_grynechko — Thu, 21 Nov 2019 20:19:04 GMT

Hey,

do you want to have only a pairs of Col1,Col2 or Col1,Col2 and Col2,Col1?

Re: Combinations of numbers in 1 column

jheasley — Thu, 21 Nov 2019 20:45:44 GMT

try this method:

load your data in, lets assume there is a key and 4 number columns (key,c1, c2, c3, c4).

make a straight table for each set combinations with the following DIMENSION columns:

c1&'|'c2
rowno(total) - this will give you the number of permutations)

next table:
c1&'|'&c2&'|'&c3
rowno(total)

repeat until you have figured out all of your combinations.

Re: Combinations of numbers in 1 column

Gopi_E — Fri, 22 Nov 2019 06:54:49 GMT

hey,

try to use composite key

Re: Combinations of numbers in 1 column

y_grynechko — Fri, 22 Nov 2019 16:18:14 GMT

Thanks for a cool question, had a lot of fun automating it, solution below:

Table:
load * inline [
col1, col2, col3, col4,

1, 1, 7, 10

1, 2, 5, 15

1, 3, 6, 15

1, 4, 8, 10

];

LET vFieldNo = NoOfFields('Table');

set i = 1;
set k = 2;

do while i <= $(vFieldNo)
AllNumbers:
LOAD Distinct col$(i) as Numbers
Resident Table;
let i = $(i)+1;
loop

let i = 1;

do while k<= $(vFieldNo)

let j = $(vFieldNo)-$(k); //2 with 4col

do while j< $(vFieldNo)
if i = 1 then
Pairs$(k):
LOAD Numbers as [Numbers$(i)-$(k)]
Resident AllNumbers;
else
join (Pairs$(k))
LOAD Numbers as [Numbers$(i)-$(k)]
Resident AllNumbers;
end if
let j = $(j)+1;
let i = $(i)+1;
loop

let k = $(k)+1;
let i = 1;
loop

DROP Tables Table, AllNumbers;