How do i create possible combination of company where each company

[rexxar]

how do i create possible combination of company

where the company forming into the combination must come from different event with explanation below

e.g. for case of group a

tender1, tender2, tender3

coke       fanta        fanta

tender1, tender2, tender3

Sptie,      fanta      fanta

tender1, tender2, tender3

fanta,      fanta      fanta

In order to get a pass result:

-all 3 different distinct company ( coke, sprite, fanta ) must exist within a group

-order does not matter( sprite, coke, fanta ) or ( fanta, coke, sprite ) still get a pass

sample data:

group	event	company
a	1	coke
a	1	sprite
a	1	fanta
a	2	fanta
a	3	fanta
b	4	coke
b	4	sprite
b	5	sprite
b	6	fanta
c	7	coke
c	7	sprite
c	8	sprite
c	8	fanta
c	9	fanta

sample output:

group	possible combination	Individual result	Final result
a	coke, fanta, fanta	fail	fail
a	sprite, fanta, fanta	fail
a	fanta, fanta, fanta	fail
b	coke, sprite, fanta	pass	pass
b	sprite, sprite, fanta	fail
c	coke, sprite, fanta	pass	pass
c	coke, fanta, fanta	fail
c	sprite, fanta, fanta	fail

[Dataintellinalytics]

Attached is an UI implementation same can be moved to backend script. 

It should Concat of Company group by Group and Event.

=Concat(company,',')

=If( Rangesum(SubStringCount(Concat(distinct company,'|'),'fanta'),
SubStringCount(Concat(distinct company,'|'),'coke'),
SubStringCount(Concat(distinct company,'|'),'sprite')) =3, 'Pass','Fail')

[stormseekerx]

It worked partly but not fully as intended.

A) for this logic:

=Concat(company,',')

for group a, the logic creates coke, fanta, sprite

but by right

group a should contains

coke, fanta, fanta

sprite, fanta, fanta

fanta, fanta, fanta

group a should fail the test because there should be no coke, sprite, fanta combination

B) for this logic:

=If( Rangesum(SubStringCount(Concat(distinct company,'|'),'fanta'),
SubStringCount(Concat(distinct company,'|'),'coke'),
SubStringCount(Concat(distinct company,'|'),'sprite')) =3, 'Pass','Fail')

it works! but in this case its hardcoded to fanta, sprite, coke.

what if the drinks can be anything ( e.g. 7up, poka, a&w )

[Dataintellinalytics]

"group a should contains

coke, fanta, fanta

sprite, fanta, fanta

fanta, fanta, fanta"   How are you deriving this ?

[Dataintellinalytics]

" what if the drinks can be anything ( e.g. 7up, poka, a&w ) "   - you have to hardcode I don't see any other option.

[stormseekerx]

This is what is used in script editor:

coke:
LOAD
group,
event,
company,
group & '|' & event as key_1

FROM [lib://DataFiles/coke.xlsx]
(ooxml, embedded labels, table is Sheet1);

/////////////////////////////////////////////////////////

map_1:
// left join(coke)
Load group, event,Concat("company", ',') as concat_supplier_name

Resident coke

Group By group, event;

drop table coke;

This is what is in model model view:

[Dataintellinalytics]

So your model view matches with my earlier output snapshot right ? not with below .

There seems to be a mistake in below group derivation, hence asked for logic.

"group a should contains

coke, fanta, fanta

sprite, fanta, fanta

fanta, fanta, fanta" 

[stormseekerx]

The logic was explained at the very start of the post, for example below:

-Get all the possible combination within a group

-the condition is that the combination must be made up of company coming from different event

e.g. all the possible combination below

group A

tender1, tender2, tender3

coke        fanta        fanta

group A

event 1 ,    event 2 ,    event 3

Sprite   ,      fanta         fanta

group A

event 1 , event 2 ,    event 3

fanta,       fanta          fanta

Noticed that for group A, there is no such combination ( coke, fanta sprite ) shown in the snapshot

as event 2 and event 3 only have fanta

it will always be just ( coke, fanta, fanta ) ,  (sprite, fanta, fanta ), ( fanta, fanta, fanta )