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() script with several where conditions

Hi Guys,

I still don't get that problem solved, to get several where conditions in one expression sum()


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

Tags (3)
1 Reply
Gysbert_Wassenaar
Not applicable

Re: sum() script with several where conditions

When you use both OR and AND you need to use parentheses to make sure you get the right result. A or B and C can mean different things:

  • (A or B) and C has two solutions:
    • A and C
    • B and C
  • A or (B and C) has two different solutions:
    • A
    • B and C

talk is cheap, supply exceeds demand