topic Re: IF and SUM in script load from SQL in QlikView

IF and SUM in script load from SQL

Anonymous — Mon, 25 Feb 2013 14:42:18 GMT

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;

Re: IF and SUM in script load from SQL

Gysbert_Wassenaar — Mon, 25 Feb 2013 14:54:23 GMT

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.

Re: IF and SUM in script load from SQL

Mon, 25 Feb 2013 14:55:53 GMT

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;

Re: IF and SUM in script load from SQL

Anonymous — Mon, 25 Feb 2013 15:33:23 GMT

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?

Re: IF and SUM in script load from SQL

hic — Mon, 25 Feb 2013 15:57:03 GMT

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

HIC

Re: IF and SUM in script load from SQL

Anonymous — Mon, 25 Feb 2013 16:07:06 GMT

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
;

Re: IF and SUM in script load from SQL

Anonymous — Mon, 25 Feb 2013 16:09:46 GMT

That works!

Thank you very much 🙂

Re: IF and SUM in script load from SQL

techvarun — Tue, 16 Jul 2013 09:46:47 GMT

Very useful post

Regards