Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

claus_beck
New Contributor III

IF and SUM in script load from SQL

Hi

I have made somecalculated fields in some charts, which I would like to use throughout my document, so I would like to add them add them to my script.

Based on the two fields "SALESPRICE" and "SALESQTY" I was able to calculate and add the field "Sales Amount" to my script, but when I try to add "Total Discount" and "Net Sales Amount" (Which are shown as disabled below) my script fails - I get the error message "invalid expression"

Is it not possible to use the IF and SUM function the way I try or have I just made a rookie mistake :-)

Appreciate your help

/Claus

 

LOAD

CUSTACCOUNT,
SALESID,
SALESPRICE,
SALESQTY,
SALESPRICE * SALESQTY as [Sales Amount],
LINEDISC,
LINEPERCENT,
LINENUM,
SALESSTATUS,
// if(LINEPERCENT > 0, SUM((LINEPERCENT/100) * (SALESQTY * SALESPRICE)), SUM(LINEDISC)) as [Total Discount],
// (SUM(SALESQTY * SALESPRICE) - if(LINEPERCENT > 0, SUM((LINEPERCENT/100) * (SALESQTY * SALESPRICE)), SUM(LINEDISC))) as [Net Sales Amount],
ITEMID,

 

SQL

SELECT CUSTACCOUNT, SALESID, SALESPRICE, SALESQTY, LINEDISC, LINEPERCENT, LINENUM, SALESSTATUS, ITEMID
FROM "KBNBSAXO01_OneRetail_Prod".dbo.SALESLINE;

   

Tags (4)
1 Solution

Accepted Solutions
sbobbyraj
Contributor III

Re: IF and SUM in script load from SQL

Hi,

Try this:


TABLE1:
SQL
SELECT
CUSTACCOUNT, SALESID, SALESPRICE, SALESQTY, LINEDISC, LINEPERCENT, LINENUM, SALESSTATUS, ITEMID
FROM
"KBNBSAXO01_OneRetail_Prod".dbo.SALESLINE
;


TABLE2:
LOAD
*,
SALESPRICE * SALESQTY as [Sales Amount],
sum(if(LINEPERCENT > 0,(LINEPERCENT/100) * (SALESQTY * SALESPRICE), LINEDISC) as [Total Discount],
SUM(SALESQTY * SALESPRICE) - SUM(if(LINEPERCENT > 0, (LINEPERCENT/100) * (SALESQTY * SALESPRICE), LINEDISC)) as [Net Sales Amount]
RESIDENT
TABLE1
GROUP BY
CUSTACCOUNT,
SALESID,
SALESPRICE,
SALESQTY,
LINEDISC,
LINEPERCENT,
LINENUM,
SALESSTATUS,
ITEMID
;


DROP TABLE TABLE1;

or even this should work:

SQL
SELECT
CUSTACCOUNT,
SALESID,
SALESPRICE,
SALESQTY,
LINEDISC,
LINEPERCENT,
LINENUM,
SALESSTATUS,
ITEMID,
SALESPRICE * SALESQTY as [Sales Amount],
sum(if(LINEPERCENT > 0,(LINEPERCENT/100) * (SALESQTY * SALESPRICE), LINEDISC) as [Total Discount],
SUM(SALESQTY * SALESPRICE) - SUM(if(LINEPERCENT > 0, (LINEPERCENT/100) * (SALESQTY * SALESPRICE), LINEDISC)) as [Net Sales Amount]
FROM
"KBNBSAXO01_OneRetail_Prod".dbo.SALESLINE
GROUP BY
CUSTACCOUNT,
SALESID,
SALESPRICE,
SALESQTY,
LINEDISC,
LINEPERCENT,
LINENUM,
SALESSTATUS,
ITEMID
;


7 Replies

Re: IF and SUM in script load from SQL

The problem is not sum and if in one expression. The problem is that if you use an aggregation function like sum, then all fields not used in aggregation functions need to be listed in a group by clause. But you can't use a group by with a preceding load. So you need to do the summing in the sql statement or use a resident load.

There's also a comma after ITEMID instead of a semicolon.

Edit: as Henric pointed out below I'm wrong about using a group by with a preceding load. It is possible. That doesn't mean it's a good idea though.


talk is cheap, supply exceeds demand
Not applicable

Re: IF and SUM in script load from SQL

When you use the function SUM(), you need to put in your script the sintaxe GROUP_BY.

Try use the IF statment inside the function SUM()

SUM( IF( LINEPERCENT > 0, ( LINEPERCENT/100 ) * ( SALESQTY * SALESPRICE ), LINEDISC )  )

Ex.:

LOAD

  CUSTACCOUNT,

  SALESID,

  SALESPRICE,

  SALESQTY,

  SALESPRICE * SALESQTY as [Sales Amount],

  LINEDISC,

  LINEPERCENT,

  LINENUM,

  SALESSTATUS,

  ITEMID,

  SUM( IF( LINEPERCENT > 0, ( LINEPERCENT/100 ) * ( SALESQTY * SALESPRICE ), LINEDISC ) ) as [Total Discount],

  SUM(SALESQTY * SALESPRICE) - SUM( IF( LINEPERCENT > 0, ( LINEPERCENT/100 ) * ( SALESQTY * SALESPRICE ), LINEDISC ) ) as [Net Sales Amount]

SQL

  SELECT CUSTACCOUNT, SALESID, SALESPRICE, SALESQTY, LINEDISC, LINEPERCENT, LINENUM, SALESSTATUS, ITEMID

  FROM "KBNBSAXO01_OneRetail_Prod".dbo.SALESLINE;

GROUP BY

  CUSTACCOUNT,

  SALESID,

  SALESPRICE,

  SALESQTY,

  SALESPRICE * SALESQTY as [Sales Amount],

  LINEDISC,

  LINEPERCENT,

  LINENUM,

  SALESSTATUS,

  ITEMID;

claus_beck
New Contributor III

Re: IF and SUM in script load from SQL

Thank you both of you for your help - It's much appreciated.

I've tried putting the If function into a sum and added the Group By function. It is however like the Group By function doesn't work

I get the error "OLEDB read failed" (which I also got before) when I'm trying the following:

LOAD CUSTACCOUNT,
SALESID,
SALESPRICE,
SALESQTY,
SALESPRICE * SALESQTY as [Sales Amount],
LINEDISC,
LINEPERCENT,
LINENUM,
SALESSTATUS,
SUM( IF( LINEPERCENT > 0, ( LINEPERCENT/100 ) * ( SALESQTY * SALESPRICE ), LINEDISC ) ) as [Total Discount],
    SUM(SALESQTY * SALESPRICE) - SUM( IF( LINEPERCENT > 0, ( LINEPERCENT/100 ) * ( SALESQTY * SALESPRICE ), LINEDISC ) ) as [Net Sales Amount],
    ITEMID;
 
SQL SELECT CUSTACCOUNT, SALESID, SALESPRICE, SALESQTY, LINEDISC, LINEPERCENT, LINENUM, SALESSTATUS, ITEMID
FROM "KBNBSAXO01_OneRetail_Prod".dbo.SALESLINE;

GROUP BY
  CUSTACCOUNT,
  SALESID,
  SALESPRICE,
  SALESQTY,
  SALESPRICE * SALESQTY as [Sales Amount],
  LINEDISC,
  LINEPERCENT,
  LINENUM,
  SALESSTATUS,
  ITEMID;

 

Considering what @Gysbert wrote is it then possible to use the Group By function when it's a preceding load?

Employee
Employee

Re: IF and SUM in script load from SQL

You can have a group by in a preceding load. But you should still do it in the SELECT - It's faster.

HIC

sbobbyraj
Contributor III

Re: IF and SUM in script load from SQL

Hi,

Try this:


TABLE1:
SQL
SELECT
CUSTACCOUNT, SALESID, SALESPRICE, SALESQTY, LINEDISC, LINEPERCENT, LINENUM, SALESSTATUS, ITEMID
FROM
"KBNBSAXO01_OneRetail_Prod".dbo.SALESLINE
;


TABLE2:
LOAD
*,
SALESPRICE * SALESQTY as [Sales Amount],
sum(if(LINEPERCENT > 0,(LINEPERCENT/100) * (SALESQTY * SALESPRICE), LINEDISC) as [Total Discount],
SUM(SALESQTY * SALESPRICE) - SUM(if(LINEPERCENT > 0, (LINEPERCENT/100) * (SALESQTY * SALESPRICE), LINEDISC)) as [Net Sales Amount]
RESIDENT
TABLE1
GROUP BY
CUSTACCOUNT,
SALESID,
SALESPRICE,
SALESQTY,
LINEDISC,
LINEPERCENT,
LINENUM,
SALESSTATUS,
ITEMID
;


DROP TABLE TABLE1;

or even this should work:

SQL
SELECT
CUSTACCOUNT,
SALESID,
SALESPRICE,
SALESQTY,
LINEDISC,
LINEPERCENT,
LINENUM,
SALESSTATUS,
ITEMID,
SALESPRICE * SALESQTY as [Sales Amount],
sum(if(LINEPERCENT > 0,(LINEPERCENT/100) * (SALESQTY * SALESPRICE), LINEDISC) as [Total Discount],
SUM(SALESQTY * SALESPRICE) - SUM(if(LINEPERCENT > 0, (LINEPERCENT/100) * (SALESQTY * SALESPRICE), LINEDISC)) as [Net Sales Amount]
FROM
"KBNBSAXO01_OneRetail_Prod".dbo.SALESLINE
GROUP BY
CUSTACCOUNT,
SALESID,
SALESPRICE,
SALESQTY,
LINEDISC,
LINEPERCENT,
LINENUM,
SALESSTATUS,
ITEMID
;


claus_beck
New Contributor III

Re: IF and SUM in script load from SQL

That works!

Thank you very much :-)

techvarun
Valued Contributor II

Re: IF and SUM in script load from SQL

Very useful post

Regards

Community Browser