topic Sum within load script in QlikView

Sum within load script

hansdevr — Mon, 13 Jun 2016 10:21:29 GMT

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

Re: Sum within load script

swuehl — Mon, 13 Jun 2016 10:38:56 GMT

LOAD [Dossier#],

[Payment date],

Sum(Amount) as TotalAmount

RESIDENT YourTable

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

Re: Sum within load script

hansdevr — Mon, 13 Jun 2016 10:43:02 GMT

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!!

Re: Sum within load script

ajsjoshua — Mon, 13 Jun 2016 10:45:07 GMT

Hi hansdevr

Could u explain further.

Regards,

Joshua.

Re: Sum within load script

swuehl — Mon, 13 Jun 2016 10:46:15 GMT

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];

Re: Sum within load script

hansdevr — Mon, 13 Jun 2016 10:47:52 GMT

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

Re: Sum within load script

Mon, 13 Jun 2016 10:51:00 GMT

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

Re: Sum within load script

hansdevr — Mon, 13 Jun 2016 10:59:18 GMT

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

Re: Sum within load script

hansdevr — Mon, 13 Jun 2016 11:19:06 GMT

No, that's not very important..