Consolidating values in script

[#1]

Bit of a noob question - this probably should be really simple, but it's got me stuck!!

As a quick example of my problem, let's say I've loaded Table1:

SalesPerson	Sales
A	10
B	15
C	3
D	8

and Table2:

Region	SalesPerson
X	A
X	B
Y	C
Y	D

all I want to do in my script is to build a new Table3: that shows this:

Region	Sales
X	25
Y	11

Obviously easy when using a chart object, but I need to do it all in the script as it's intended to run overnight on a schedule and finish by outputting Table3 to a text file ready to be picked up by another application.

I've not posted the full code as there's a helluva lot more in there, however everything else works fine so no point complicating things - I've just got this little problem to solve which probably comes down to just using the right function!

Thanks in advance for any help!

[gussfish · Accepted solution]

Something like this should do the trick.

JoinedTables:

LOAD

SalesPerson

Region

resident Table2;

LEFT JOIN

LOAD

SalesPerson,

Sales

resident Table1;

Table3:

LOAD

Region,

SUM(Sales)

resident JoinedTables

GROUP BY Region;

DROP TABLE JoinedTables;

[#3]

Cheers mate - works perfectly!

Understand it now too - I was on the right track using GROUP BY, but it was the aggregation of the 'Sales' field I was missing in my script. Knew it would be something simple enough!

Jay