Group By and Sum of Joined Resident table is creating two entries per group not one

[#1]

I have a data set that looks like this:

Date, Type, Amount

1/1/2013, A, £5

1/1/2013, A, £2

1/1/2013, B, £3

1/1/2013, B, £1

1/2/2013, A, £9

1/2/2013, A, £2

1/2/2013, B, £6

1/2/2013, B, £4

I need to transform this table so the result has additional rows in the format: Group by Date, Group by Type, SUM Amount multiplied by -1

Thus the additional rows should be in the resultant table:

1/1/2013, A, £-7

1/1/2013, B, £-4

1/2/2013, A, £-11

1/2/2013, B, £-10

However the script I'm using produces the following results:

1/1/2013, A, £-7

1/1/2013, A, £7

1/1/2013, B, £-4

1/1/2013, B, £4

1/2/2013, A, £-11

1/2/2013, A, £11

1/2/2013, B, £-10

1/2/2013, B, £10

The aggregate is creating two records per grouping!  One with the correct negative sum Amount and one with a positive sum Amount.  Why would this be happening and how can I stop it?

Many thanks,  Matt

The script I am using is like this:

Data:

LOAD
     Date,

     Type,

     Amount;

SQL SELECT

     ...

FROM Data;

Join (Data)

LOAD

     Date,

     Type,

     SUM ( Amount ) * -1 as Amount

Resident Data group by Type, Date;

[MK_QSL]

Temp:

Load

  Date(Date#(Date,'D/M/YYY')) as Date,

  Type,

  Amount

Inline

[

  Date, Type, Amount

  1/1/2013, A, 5

  1/1/2013, A, 2

  1/1/2013, B, 3

  1/1/2013, B, 1

  1/2/2013, A, 9

  1/2/2013, A, 2

  1/2/2013, B, 6

  1/2/2013, B, 4

];

NoConcatenate

Final:

Load

  Date,

  Type,

  SUM(Amount)*-1 as TotalAmount

Resident Temp

Group By Date, Type;

Drop Table Temp;

[MarcoWedel]

Hi,

if you really like to include aggregated values in your data table, then you can do so like:

Data:
LOAD * Inline [
Date, Type, Amount
1/1/2013, A, £5
1/1/2013, A, £2
1/1/2013, B, £3
1/1/2013, B, £1
1/2/2013, A, £9
1/2/2013, A, £2
1/2/2013, B, £6
1/2/2013, B, £4
];

LOAD Date,
    Type,
    Money(-Sum(Amount), '£#.##0') as Amount
Resident Data
Group By Date, Type;

Using (auto)concatenation rather than joining.

hope this helps

regards

Marco

[#4]

I ended up adjusting the LOAD statement to include 'Where Amount < 0' and it has stripped out the unwanted rows