Create combinations of a set

[markgraham123]

Hi Team,

I'm trying to create all possible combinations of a number set. Can someone please help?

[markgraham123]

@sunny_talwar Can you please help? 🙂

[stevejoyce]

Try this.   This gets all your data calculated.  If you want to have a 1-n combination fields, i can update it to create an ID for each combination in a list and can do a generic load.  let me know how you expect to use this data, but this calculates all the combinations.

input:
load * inline [
list
2,7,11
5,8,17
3,8,17
2,11
2
11
] (delimiter is '|')
;

//creates rows for each item in list
item_rows:
load
list
,subfield(list, ',') as items
resident input;

//cartesian product for all values for each list
left join (item_rows)
load
list
,items as items2
resident item_rows;

//when num 1 < num 2 keep its combination
output:
load
list
,items & ',' & items2 as combination
resident item_rows
where items < items2;

//add lists that have only 1 value
concatenate(output)
load
list
,list as combination
resident input
where substringcount(list, ',') = 0;

drop table item_rows;
drop table input;

[markgraham123]

Hi @stevejoyce , This works for the above data. But not if I have a list with 4 digits : 1,2,7,20

The above codes generates sets of 2's. I want to look at combinations dynamically.

For the example: 1,2,7,20 

[stevejoyce · Accepted solution]

Well this does seem a bit convoluted to me but i think this should work.  i expanded what i said originally but wrapped in a loop and created dynamic variables to handle n number of items in a list.  please check this out.

input:
load * inline [
list
2,11
2,7,11
5,8,17
3,8,17
2,11
2
11
1,2,7,20
1,2,3,4,5,6
] (delimiter is '|')
;

max_list_items:
load
max(substringcount(list, ',')) as max_list_items
resident input;

/*

*/

//creates rows for each item in list
item_rows:
load
list
,subfield(list, ',') as items1
resident input;

let maxitems = peek('max_list_items', 0, 'max_list_items');

//initial syntax
set vcombination_calc = items1;
set vcombination_whereclause = 1=1;

Trace vcombination_whereclause $(vcombination_whereclause);

for i = 2 to ($(maxitems))


//after first syntax
let vcombination_calc = '$(vcombination_calc)' & ' & ' & chr(39) & ',' & chr(39) & '& items' & $(i) ;
let vprevious_item = $(i) - 1;
let vcombination_whereclause = '$(vcombination_whereclause)' & ' and ' & '( items' & $(vprevious_item) & ' < items' & $(i) & ' )';

Trace vcombination_calc $(vcombination_calc);
Trace vcombination_whereclause $(vcombination_whereclause);

//cartesian product for all values for each list
left join (item_rows)
load distinct
list
,items1 as items$(i)
resident item_rows
;

//when num 1 < num 2 keep its combination

output:
load distinct
list
,$(vcombination_calc) as combination
//,items & ',' & items2 & ',' & items3 as combination
resident item_rows

where $(vcombination_whereclause);

next i

//add lists that have only 1 value
concatenate(output)
load
list
,list as combination
,1 as list_combination_id
resident input
where substringcount(list, ',') = 0;

drop table item_rows;
drop table input;

exit script;

[markgraham123]

Thank you @stevejoyce . You are awesome! Really appreciate your help!