Table reduction using distinct- help?

[#1]

Hi,

I am pretty sure I am doing something incorrectly in script here but can't work out what. I am trying to do a resident load from one table, summing values from a previous table.

I want to change this:

CustomerID	Product	Month	C1	C2	C3	C4	C5
A	1	jan.09	100	0	0	0	0
A	1	jan.09	0	200	0	0	0
A	1	jan.09	0	0	300	0	0

into this:

CustomerID	Product	Month	C1	C2	C3	C4	C5
A	1	jan.09	100	200	300	0	0

The code I am using is:

BUSINESSNEW4:

LOAD

DISTINCT

CUSTOMERID

,

PRODUCT,

MONTH,

SUM(C1) AS C1,

SUM

(C2) AS C2,

SUM

(C3) AS C3,

SUM

(C4) AS C4,

SUM

(C5) AS C5

RESIDENT

GROUP

DROP

TABLE

BUSINESSNEW3;

BY CUSTOMERID, PRODUCT, MONTH;BUSINESSNEW3

The script execution progress states the following:

BUSINESSNEW3 << BUSINESSNEW2 37,596 lines fetched

BUSINESSNEW3 << BUSINESSNEW3 73,343 lines fetched

It is almost doubling the number of lines and not recognising the name BUSINESSNEW4.

I can't see where the problem is. Any ideas?

Thanks

[#2 · Accepted solution]

Hi,

here are an working example of your data. Hope it helps to find out the difference.

Merry Christmas!

Rainer

[vidyut]

Add one more field

Select 1 as TmpID, .....

In the New Table, and Load again. Should work.

[#4]

Thanks Rainer.

I did not realise that you can not give a calculated field the same name as the field it is calculating from, but your example works great!

Merry Christmas to you too.

R

[#5]

Vidyut, same reply to you. Your solution works as well.

Many thanks for your help.

R