Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sculptorlv
Creator III
Creator III

SQL ... sum after sum

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!

1 Solution

Accepted Solutions
Not applicable

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

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

Or

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

ON

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

GROUP BY

     Customer_Payment."Customer No_"

;

View solution in original post

5 Replies
florentina_doga
Partner - Creator III
Partner - Creator III

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_";

Not applicable

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

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

Or

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

ON

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

GROUP BY

     Customer_Payment."Customer No_"

;

Not applicable

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!

sculptorlv
Creator III
Creator III
Author

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?

Not applicable

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