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

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;

   

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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
;


View solution in original post

7 Replies
Gysbert_Wassenaar

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
Author

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;

Anonymous
Not applicable
Author

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?

hic
Former Employee
Former Employee

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

HIC

Anonymous
Not applicable
Author

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
;


Anonymous
Not applicable
Author

That works!

Thank you very much 🙂

techvarun
Specialist II
Specialist II

Very useful post

Regards