topic Re: Create combinations of a set in App Development

Create combinations of a set

markgraham123 — Tue, 30 Nov 2021 16:25:32 GMT

Hi Team,

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

Re: Create combinations of a set

markgraham123 — Tue, 31 Aug 2021 17:01:54 GMT

@sunny_talwar Can you please help? 🙂

Re: Create combinations of a set

stevejoyce — Tue, 31 Aug 2021 17:26:54 GMT

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;

Re: Create combinations of a set

markgraham123 — Tue, 31 Aug 2021 18:53:00 GMT

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

Re: Create combinations of a set

stevejoyce — Tue, 31 Aug 2021 19:33:09 GMT

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;

Re: Create combinations of a set

markgraham123 — Tue, 31 Aug 2021 21:03:37 GMT

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