Combinations of numbers in 1 column

[LeeSmithBtn]

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

[y_grynechko]

Hey,

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

[jheasley]

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.

[Gopi_E]

hey,

try to use composite key 

[y_grynechko]

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;