Solution might be depending a bit on the real data, assuming that the second table has for each call max one entry for either company1 or company2 (or both), you may work with multiples of 2: assign value 1 to company1, value 2 to company2 (value 4 to company3) and then sum up the values. If result is 1, you have only company1. If result is 2, there is only company2, if 3 they are mixed.
Thanks Peter, but i've proabably oversimplified things above. I pull the info from a SQL database, and there can be up to 50 diffrent rows in the second table relating to various departments of companies 1 & 2.
Is there any way of reading the lines of data as i bring them in, in some kind of loop FOR ... NEXT... etc... to create rows in a new 'summary' table?
I have other similar situations where i can see this requirement as well.
If you can be sure that there will only ever be two companies in the second table you could use a couple of mapping loads onto the main call table. It would be something like this:
1 as CallCount
WHERE Location = 'Company1';
[Similar mapping load for Company 2 Calls]
ApplyMap('Map_Company1Calls', ID, 0) as Company1Count,
ApplyMap('Map_Company2Calls', ID, 0) as Company2Count
Once you've done that you've two fields you can sum for Company 1 Count and Company 2 Count. You can then either use logic on those two flags to work out how many calls are for both companies, or you could put the following preceding load on the calls table:
if(Company1Count + Company2Count > 1, 1, 0) as BothCompaniesCount,
if(Company1Count + Company2Count = 0, 1, 0) as NeitherCompaniesCount;
Hope that all makes some kind of sense. Post back here if you need any further pointers.