topic How do i create possible combination of company where each company in App Development

How do i create possible combination of company where each company

rexxar — Fri, 15 Nov 2024 21:02:32 GMT

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	pass
c	coke, sprite, fanta	pass	pass
c	coke, fanta, fanta	fail
c	sprite, fanta, fanta	fail

Re: How do i create possible combination of company where each company

Dataintellinalytics — Sun, 03 Mar 2024 11:54:21 GMT

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')

Re: How do i create possible combination of company where each company

stormseekerx — Sun, 03 Mar 2024 14:59:07 GMT

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 )

Re: How do i create possible combination of company where each company

Dataintellinalytics — Sun, 03 Mar 2024 15:11:20 GMT

"group a should contains

coke, fanta, fanta

sprite, fanta, fanta

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

Re: How do i create possible combination of company where each company

Dataintellinalytics — Sun, 03 Mar 2024 16:09:50 GMT

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

Re: How do i create possible combination of company where each company

stormseekerx — Mon, 04 Mar 2024 01:22:41 GMT

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:

Re: How do i create possible combination of company where each company

Dataintellinalytics — Mon, 04 Mar 2024 02:26:15 GMT

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"

Re: How do i create possible combination of company where each company

stormseekerx — Mon, 04 Mar 2024 09:01:04 GMT

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 )