Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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..

Tags (3)
7 Replies
MK_QSL
Not applicable

Re: sum() im Script with where condition ?

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

tresesco
Not applicable

Re: sum() im Script with where condition ?

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

See: New value as quotient of other two value

Clever_Anjos
Not applicable

Re: sum() im Script with where condition ?

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

Re: sum() im Script with where condition ?

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

Re: sum() im Script with where condition ?

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

Re: sum() im Script with where condition ?

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

Re: sum() im Script with where condition ?

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()?