topic Re: SQL ... sum after sum in QlikView

SQL ... sum after sum

sculptorlv — Wed, 20 Jan 2016 14:03:44 GMT

Hello and please help.

I have a code in my SQL

LOAD

IF(Money > 0, 'Debitor','ok') AS Debitor_sign,

Customer_number;

SQL SELECT

Customer_Payment."Customer No_" AS Customer_number,

Customer_Payment."Document No_" AS Document_number,

Customer_Payment."Document Date" AS Document_date,

Customer_Payment."Due Date" AS Document_due_date,

SUM(Customer_Payment_Rows."Amount (LCY)") AS Money

FROM *$Cust_ Ledger Entry" AS Customer_Payment

LEFT OUTER JOIN *$Detailed Cust_ Ledg_ Entry" AS Customer_Payment_Rows

ON Customer_Payment_Rows."Cust_ Ledger Entry No_" = Customer_Payment."Entry No_";

GROUP BY

Customer_Payment."Customer No_",

Customer_Payment."Document No_",

Customer_Payment."Document Date",

Customer_Payment."Due Date";

As a result I get:

Customer Money

123 +5

123 +3

123 +1

456 - 5

...

How can I get another SUM in order to get:

Customer Money

123 + 9

456 -5

Thank you in advance!

Re: SQL ... sum after sum

florentina_doga — Wed, 20 Jan 2016 14:15:08 GMT

exclude document_number and document_data, and due date

SQL SELECT

Customer_Payment."Customer No_" AS Customer_number,

SUM(Customer_Payment_Rows."Amount (LCY)") AS Money

FROM *$Cust_ Ledger Entry" AS Customer_Payment

LEFT OUTER JOIN *$Detailed Cust_ Ledg_ Entry" AS Customer_Payment_Rows

ON Customer_Payment_Rows."Cust_ Ledger Entry No_" = Customer_Payment."Entry No_";

GROUP BY

Customer_Payment."Customer No_";

Re: SQL ... sum after sum

Wed, 20 Jan 2016 14:16:29 GMT

Try like Below:

LOAD

IF(Money > 0, 'Debitor','ok') AS Debitor_sign,

Customer_number,

Money

;

LOAD

Customer_number,

SUM(Money) AS Money

Group By

Customer_number

;

SQL

SELECT

Customer_Payment."Customer No_" AS Customer_number,

Customer_Payment."Document No_" AS Document_number,

Customer_Payment."Document Date" AS Document_date,

Customer_Payment."Due Date" AS Document_due_date,

SUM(Customer_Payment_Rows."Amount (LCY)") AS Money

FROM

*$Cust_ Ledger Entry" AS Customer_Payment

LEFT OUTER JOIN

*$Detailed Cust_ Ledg_ Entry" AS Customer_Payment_Rows

Customer_Payment_Rows."Cust_ Ledger Entry No_" = Customer_Payment."Entry No_";

GROUP BY

Customer_Payment."Customer No_",

Customer_Payment."Document No_",

Customer_Payment."Document Date",

Customer_Payment."Due Date";

directly sum all the values in the SQL query itself like below:

LOAD

IF(Money > 0, 'Debitor','ok') AS Debitor_sign,

Customer_number,

Money

;

SQL

SELECT

Customer_Payment."Customer No_" AS Customer_number,

SUM(Customer_Payment_Rows."Amount (LCY)") AS Money

FROM

*$Cust_ Ledger Entry" AS Customer_Payment

LEFT OUTER JOIN

*$Detailed Cust_ Ledg_ Entry" AS Customer_Payment_Rows

Customer_Payment_Rows."Cust_ Ledger Entry No_" = Customer_Payment."Entry No_";

GROUP BY

Customer_Payment."Customer No_"

;

Re: SQL ... sum after sum

Wed, 20 Jan 2016 14:17:22 GMT

Hi,

Create a straight table and use the customer field as dimension and might be use sum(Money) as your expression or some set analysis functions like sum({<Customer=>}Money) could also help.

Hope it helps!

Re: SQL ... sum after sum

sculptorlv — Wed, 20 Jan 2016 14:22:25 GMT

uhh... I understand .. I made a syntax error. I tried:

SUM(money) .... without AS Money

Is it correct, that I can use a lot of LOAD (one after another) for one SQL?

Re: SQL ... sum after sum

Wed, 20 Jan 2016 14:29:10 GMT

You can crate "N" of LOAD statements one after other but it may degrade the load performance. It all depends the on the requirements.