Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

hansdevr
Contributor II

Sum within load script

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 dateAmount
100015-6-2016€ 1.000,00
100014-6-2016€ 500,00
100012-6-2016€ 800,00
100012-6-2016€ 600,00
100026-6-2016€ 400,00
100026-6-2016€ 350,00

   

Dossier#Payment dateAmount
100015-6-2016€ 1.000,00
100014-6-2016€ 500,00
100012-6-2016€ 1.400,00
100026-6-2016

€ 750,00

Tags (1)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Sum within load script

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

8 Replies
MVP
MVP

Re: Sum within load script

LOAD [Dossier#],

          [Payment date],

          Sum(Amount) as TotalAmount

RESIDENT YourTable

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

hansdevr
Contributor II

Re: Sum within load script

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
Contributor III

Re: Sum within load script

Hi hansdevr

Could u explain further.

Regards,

Joshua.

Highlighted
MVP
MVP

Re: Sum within load script

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
Contributor II

Re: Sum within load script

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

Not applicable

Re: Sum within load script

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

hansdevr
Contributor II

Re: Sum within load script

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

hansdevr
Contributor II

Re: Sum within load script

No, that's not very important..