Create combinations of a set based on item count

[markgraham123]

Hi Community, 

I have a data set where I need to create combinations based on the count of items I have.

As I have 3 distinct items, my combinations should be in a set of 3.

1 ID for each item. Can someone please help? I have attached the sample QS file.  Any help is highly appreciated. @stevejoyce @sunny_talwar 

Sample Input:

Container, item, ID
C1, A, 1
C1, A, 3
C1, B, 3
C1, B, 5
C1, C, 2
C1, C, 6

Sample Output:

Container	Combo	Values
C1	ABC	1,3,2
C1	ABC	1,3,6
C1	ABC	1,5,2
C1	ABC	1,5,6
C1	ABC	3,3,2
C1	ABC	3,3,6
C1	ABC	3,5,2
C1	ABC	3,5,6

[stevejoyce · Accepted solution]

My thought is to unpivot the data, cross join, and then combine the new columns for each item.  this seems to work, maybe there's a cleaner or more straightfoward way, but i expect this to work.

input:

load * inline [
Container, item, ID
C1, A, 1
C1, A, 3
C1, B, 3
C1, B, 5
C1, C, 2
C1, C, 6

D1, A, 1
D1, A, 3
D1, C, 6
]
;

//syntax to find if field is item value
let vSpecialFieldSyntax = '<>';

//unpivot data with specialsyntax
generic_data:
generic load Container, 'Field' & '$(vSpecialFieldSyntax)' & item, ID resident input;

//join generic tables

Set vListOfTables = ;

For vTableNo = 0 to NoOfTables()

Let vTableName = TableName($(vTableNo)) ;

If Subfield(vTableName,'.',1)='generic_data' Then

Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

End If

Next vTableNo

CombinedGenericTable:

Load distinct Container Resident input;

drop table input;

For each vTableName in $(vListOfTables)

Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];

Drop Table [$(vTableName)];

Next vTableName

//dynamic variables used in load script to merge item columns
Set vListOfValues = ;
Set vListOfFields = ;

for field = 1 to NoOfFields('CombinedGenericTable')

let vFieldCheck = FieldName($(field), 'CombinedGenericTable');

Trace vFieldCheck $(vFieldCheck);

if Subfield(vFieldCheck, '$(vSpecialFieldSyntax)',1) = 'Field' Then

let vListOfValues = vListOfValues & If(Len(vListOfValues)>0,' & ') & 'If(Len([' & vFieldCheck & ']) > 0,' & '[' & vFieldCheck & ']' & '& ' & chr(39) & ',' & chr(39) & ')';
let vListOfFields = vListOfFields & If(Len(vListOfFields)>0,' & ') & 'If(Len([' & vFieldCheck & ']) > 0,' & chr(39) & Subfield(vFieldCheck, '$(vSpecialFieldSyntax)',2) & chr(39) & ')';

trace $(vListOfFields) vListOfFields;

end if;

next field

final_table:
load
Container,
$(vListOfFields) as Combo,
left($(vListOfValues), len($(vListOfValues))-1) as Values
resident CombinedGenericTable
;

drop table CombinedGenericTable;

[markgraham123]

Perfect! This works like magic! Thanks @stevejoyce !