Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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];
LOAD [Dossier#],
[Payment date],
Sum(Amount) as TotalAmount
RESIDENT YourTable
GROUP BY [Dossier#], [Payment date];
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!!
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];
OK, gonna try that and will get back to you!
Do you want the aggregated table bearing the same/different name as the original table ?
Thank you very much!! Worked like a charm!!
No, that's not very important..