Flag certain entries (orders) in load script if members of two firms cooperated in order

[TobiasTebben]

Dear community members,

my problem sounds pretty simple when stated in normal language, but I seem to be unable to find a solution for my load script.

I have two tables. The first contains my orders and the people that have worked to fulfil these orders:

OrderNo	Person	Time (h)
01	Adam	1.0
01	Bertram	1.5
01	Cederic	0.7
02	Adam	3.0
02	Dora	2.0

My second table matches the different persons to two different firms:

Person	Firm
Adam	Miller & Co.
Bertram	Miller & Co.
Cederic	Miller & Co.
Dora	Smith Ltd

My goal is to identify and flag all orders in which people from different firms have cooperated, in my example OrderNo 02. Obviously, I have to sum up the sum of time worked by firm and multiply it: because I analyse only two different firms, the result of this operation will be 0/null if not employees from both firms were involved in the order.  For these „cooperation orders“ I want to set a flag so I can use this in set analysis later on.

However, all my attempts to come up with a load script for this have failed miserably. 

I would be grateful for any help!

Many thanks in advance!

Tobi

[dplr-rn · Accepted solution]

Use script similar to below

order:
load * inline [
OrderNo,	Person,	Time (h)
01,	Adam,	1.0
01,	Bertram,	1.5
01,	Cederic,	0.7
02,	Adam,	3.0
02,	Dora,	2.0
]
;

person:
load * inline [
Person,	Firm
Adam,	Miller & Co.
Bertram,	Miller & Co.
Cederic,	Miller & Co.
Dora,	Smith Ltd

];

NoConcatenate
temp:
load *
resident person;
Right join(temp)
load OrderNo,Person
Resident order;

left join(order)
load OrderNo, count(DISTINCT Firm) as FirmCount,
if (count(DISTINCT Firm)>1,'Y','N') as Coooperate
resident temp
group by OrderNo;

drop table temp;

output

[Kushal_Chawda]

If you are interested in frond end solution. Try below expression

=Count({<OrderNo={"=count(distinct Person)= count(distinct Firm)"}>}OrderNo)