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: 
Not applicable

sum() im Script with where condition ?

Hi guys

I've following Script:

ODBC CONNECT TO DB1;

Database:
Load "Posting Date",
    
Month("Posting
Date"
) AS Month,
    
Year("Posting
Date"
) AS Year,
    
Amount,
    
sum(Amount) as ALL.AMOUNT where "G_L
Account No_"
>=400000 and "G_L Account No_"<=479999 or "Global Dimension 1 Code"= 'T83400' or "Global
Dimension 1 Code"
= 'T83500',
    Description,
   
"Document No_",
   
"Entry No_",
   
"G_L Account No_",
   
"G_L Account No_" as "DB1-Konten",
   
"Global Dimension 1 Code",
   
"Global Dimension 1 Code" as KST,
   
"Global Dimension 2 Code",
   
"Global Dimension 2 Code" as KTR

   
//-------- Start Multiple Select
Statements ------
SQL SELECT *

FROM "DB1";
//-------- End Multiple Select Statements
-----

but it doenst work ? whats my mistake?

neither with :

sum((Amount) WHERE ( "G_L Account No_">=400000 and "G_L Account No_"<=479999 or "Global Dimension 1 Code"= 'T83400' or "Global Dimension 1 Code" = 'T83500')) as ALL.AMOUNT,

and

sum(Amount) WHERE ( "G_L Account No_">=400000 and "G_L Account No_"<=479999 or "Global Dimension 1 Code"= 'T83400' or "Global Dimension 1 Code" = 'T83500') as ALL.AMOUNT,

____

My Intention is to sum() the Amount table within where conditions..

7 Replies
MK_QSL
MVP
MVP

If you have used SUM(Amount) you need to use Group By also...

tresesco
MVP
MVP

I guess you have to try like: Sum(If(......

See: New value as quotient of other two value

Clever_Anjos
Employee
Employee

It depends.

If you want all records retrieved, and rows that doesn´t match your criteria be equal 0 then you can use

sum(if("G_L Account No_">=400000 and "G_L Account No_"<=479999 or "Global Dimension 1 Code"= 'T83400' or "Global Dimension 1 Code" = 'T83500',Amount)) as ALL.AMOUNT


if you want only records that match your criteria then you have only one "where" statement, preferably at SQL level

Not applicable
Author

sum(Amount) as AmountALL WHERE("G_L Account No_">=400000 and "G_L Account No_"<=479999 or "Global Dimension 1 Code"= 'T83400' or "Global Dimension 1 Code" = 'T83500') Group By "Global Dimension 1 Code",

with that I still get the message "wrong Syntax"

Not applicable
Author

I think you should try Sum(if((G_L Account No_>=400000 and "G_L Account No_" <=479999) or "Global Dimension 1 Code"='T83400' or "Global Dimension 1 Code" = 'T83500'),Amount) as ALL.AMOUNT

MK_QSL
MVP
MVP

Consider that you have below

Load

     Customer,

     Country,

     Month,

     SUM(Sales)

From TableName

Group By Customer, Country, Month ;

i.e. You have to use group by all Fields used in ...

Not applicable
Author

Hi Guys,

I still don't get that problem.


ODBC CONNECT TO NAV;
Load Amount,
   
"Global Dimension 1 Code" as X,
   
"Global Dimension 2 Code" as Y,
   
"G_L Account No_" as Z,
   
"Posting Date",
   
Month("Posting Date") as Month,
   
Year("Posting Date") as Year,
   
sum(if("Global Dimension 1 Code" = 'T83400' or "Global
Dimension 1 Code"
='T83500' or "G_L Account No_">=400000 and "G_L
Account No_"
<=479999 , Amount)) as ALL.AMOUNT Group By Amount, "Global Dimension 1 Code", "Global
Dimension 2 Code"
,"G_L Account No_", "Posting
Date"

    ;
   
SQL SELECT Amount,
    "Global Dimension 1 Code",
    "Global Dimension 2 Code",
    "Posting Date",
    "G_L Account No_"
FROM "DB ".dbo."DB7";

_____________

I tried with

- sum(if(...)Amount) as ALL.AMOUNT;

- sum(if((...),Amount) as ALL.AMOUNT;

- sum(Amount) as ALL.AMOUNT WHERE (...) Group By ...;

___

Load "Global Dimension 1 Code",
   
"Global Dimension 2 Code",
   
"G_L Account No_", "Posting Date",

sum(Amount) as Amount.ALL WHERE("G_L Account No_">=400000 and "G_L Account No_"<=479999 or "Global Dimension 1 Code"= 'T83400' or "Global Dimension 1 Code" = 'T83500') Group By  Amount"Global Dimension 1 Code","Global Dimension 2 Code",
   
"G_L Account No_", "Posting Date" ;

______

My Intention is to filter a sum(Amount) with more then 1 condition like:

Sum(Amount) where ("X" = 'T83400', "X"='T83500' , "Z"<479999, Z>400000) ...

When I have to use OR / AND ? How can I give this conditions to one sum()?