Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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
I ended up adjusting the LOAD statement to include 'Where Amount < 0' and it has stripped out the unwanted rows