Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
;
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.
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;
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?
You can have a group by in a preceding load. But you should still do it in the SELECT - It's faster.
HIC
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
;
That works!
Thank you very much 🙂
Very useful post
Regards