Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I'm trying to create all possible combinations of a number set. Can someone please help?
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;
@sunny_talwar Can you please help? 🙂
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;
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
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;
Thank you @stevejoyce . You are awesome! Really appreciate your help!