Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to manipulate illegal field value in script

Hi,

I'm trying to add grossmargin percent calculation in edit script from ODBC database (Sql server). The problem is that sometimes sales maybe 0 for row and then the script ends up to an error. I would need to check if sales is zero then gm%=0 else the formula. I have tried the following (below) but it just says "Incorrect syntax near the keyword 'If'. Please help me on this one how to do this.

ODBC

CONNECT32 TO TUOTANTO2 (XUserId is xx, XPassword is xx);

SQL SELECT CustName,

    CustNum,

    DirVarLabCost,

    MaterialCost,

    Sales,

    if (Sales=0, 0, ((Sales-DirVarLabCost-MaterialCost)/Sales*100)) AS GMPros

FROM Tuotanto.dbo."ACS_MonthlyData";

1 Solution

Accepted Solutions
MVP
MVP

Re: How to manipulate illegal field value in script

Use a preceding LOAD:

ODBC

CONNECT32 TO TUOTANTO2 (XUserId is xx, XPassword is xx);

LOAD *,

  if (Sales=0, 0, ((Sales-DirVarLabCost-MaterialCost)/Sales*100)) AS GMPros;


SQL SELECT CustName,

    CustNum,

    DirVarLabCost,

    MaterialCost,

    Sales

FROM Tuotanto.dbo."ACS_MonthlyData";

2 Replies
MVP
MVP

Re: How to manipulate illegal field value in script

Use a preceding LOAD:

ODBC

CONNECT32 TO TUOTANTO2 (XUserId is xx, XPassword is xx);

LOAD *,

  if (Sales=0, 0, ((Sales-DirVarLabCost-MaterialCost)/Sales*100)) AS GMPros;


SQL SELECT CustName,

    CustNum,

    DirVarLabCost,

    MaterialCost,

    Sales

FROM Tuotanto.dbo."ACS_MonthlyData";

Not applicable

Re: How to manipulate illegal field value in script

Thanks for the quick reply and the solution!

It works!

Community Browser