topic How do I combine this data in the script? in QlikView

How do I combine this data in the script?

Tue, 11 Jan 2011 00:23:03 GMT

I have a horrible database that has a lot of examples of the follow:

Payment ID: 1 Account Number: 12345 Payment Date: 1/1/2009 Payment Amount: 50

Payment ID: 2 Account Number: 12345 Payment Date: 1/1/2009 Payment Amount: 100

I want to combine these two (or three or four or five) into one, so this one should be:

Payment ID: 1 Account Number: 12345 Payment Date: 1/1/2009 Payment Amount: 150

I'm really just learning how to use the script and I really don't have a clue how to fix this.

Thanks in advance.

bumin — Tue, 11 Jan 2011 00:34:53 GMT

what about if you create a pivot table with the dimensions aPayment ID, Account-No and Payment date and create a formula as sum(Amount)?

Within the script you use the SQL

select paymentID, AccountNo, paymentdate, sum(amount) from table

group by paymentID, AccountNo, paymentdate

maneshkhottcpl — Tue, 11 Jan 2011 03:35:40 GMT

Hi,

You can try the following scripts,

select min("Payment ID") as "Payment ID", sum(Amount) as Amount, "Account Number","Payment Date"

from TableName group by "Account Number", "Payment Date"

Good Luck