Sum within load script

[hansdevr]

Hi Everyone,

I have a table with payments. Main key is Dossier# and - among others - I have the fields payment date and amount payed.

I need to make a copy of this table, in which all payments for one date are summarized. See example below. Can anyone help me out about how to achieve this in the load script?

Dossier#	Payment date	Amount
10001	5-6-2016	€ 1.000,00
10001	4-6-2016	€ 500,00
10001	2-6-2016	€ 800,00
10001	2-6-2016	€ 600,00
10002	6-6-2016	€ 400,00
10002	6-6-2016	€ 350,00

Dossier#	Payment date	Amount
10001	5-6-2016	€ 1.000,00
10001	4-6-2016	€ 500,00
10001	2-6-2016	€ 1.400,00
10002	6-6-2016	€ 750,00

[swuehl]

LOAD [Dossier#],

          [Payment date],

          Sum(Amount) as TotalAmount

RESIDENT YourTable

GROUP BY [Dossier#], [Payment date];

[hansdevr]

Thanks for your swift reply, Swuehl!! How do I get back the fields from the original table, such as Status and User? Without of course, getting the orinale date duplicates back!!

[ajsjoshua]

Hi hansdevr

Could u explain further.

Regards,

Joshua.

[swuehl · Accepted solution]

If User and status are unique per Dossier# and date, you can do it like

LOAD [Dossier#],

          [Payment date],

          Sum(Amount) as TotalAmount,

          Only(User) as User,

          Only(Status) as Status

RESIDENT YourTable

GROUP BY [Dossier#], [Payment date];

[hansdevr]

OK, gonna try that and will get back to you!

[#7]

Do you want the aggregated table bearing the same/different name as the original table ? 

[hansdevr]

Thank you very much!! Worked like a charm!!

[hansdevr]

No, that's not very important..