Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
joanioconnor
New Contributor

When I use a where statement in load script that looks like this: WHERE LineType = '1' AND isnull(OpenOrderQty,0) <> 0; I run in debug mode, I get the following error message: ErrorSource: Unknown source, ErrorMsg: Unknown description SQL SELECT * FROM S

When I use a where statement  in load script that looks like this: WHERE LineType = '1' AND isnull(OpenOrderQty,0) <> 0; I run in debug mode, I get the following error message:

ErrorSource: Unknown source, ErrorMsg: Unknown description SQL SELECT * FROM SysproCompanyTurf.dbo.SorDetail WHERE LineType = '1' AND isnull(OpenOrderQty,0) <> 0.

I only want to load the data where Linetype=1 and where OpenOrderQty<> 0. And I'm a newbie to both Qlikview and SQL

OpenOrderQty is a "calulated" field in the LOAD statement before the WHERE clause

MShipQty + MBackOrderQty as OpenOrderQty,

I've tried OpenOrderQty <>0

I've tried OpenOrderQty <>'0'

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: When I use a where statement in load script that looks like this: WHERE LineType = '1' AND isnull(OpenOrderQty,0) <> 0; I run in debug mode, I get the following error message: ErrorSource: Unknown source, ErrorMsg: Unknown description SQL SELECT * FR

Hi

You can't use a where clause for a field that is being calculated in the load script.

You will need something like

Where (MShipQty <> 0 and MBackOrderQty <> 0) Or (LEN(TRIM(MShipQty)) <> 0 and LEN(TRIM(MBackOrderQty)) <> 0) AND LineType = 1.

I prefer to use Len and Trim over isnull incase of random spaces.

Mark

View solution in original post

3 Replies
Highlighted
chinnuchinni
Contributor III

Re: When I use a where statement in load script that looks like this: WHERE LineType = '1' AND isnull(OpenOrderQty,0) <> 0; I run in debug mode, I get the following error message: ErrorSource: Unknown source, ErrorMsg: Unknown description SQL SELECT * FR

try this:

(MShipQty + MBackOrderQty ) <> 0

Highlighted
sasiparupudi1
Honored Contributor III

Re: When I use a where statement in load script that looks like this: WHERE LineType = '1' AND isnull(OpenOrderQty,0) <> 0; I run in debug mode, I get the following error message: ErrorSource: Unknown source, ErrorMsg: Unknown description SQL SELECT * FR

You Can not use a calculated field in the where condition.

Try like this

SQL SELECT * FROM SysproCompanyTurf.dbo.SorDetail WHERE

LineType = '1' AND

(IsNull(MShipQty,0) +IsNUll(MBackOrderQty,0))<>0

Highlighted
Partner
Partner

Re: When I use a where statement in load script that looks like this: WHERE LineType = '1' AND isnull(OpenOrderQty,0) <> 0; I run in debug mode, I get the following error message: ErrorSource: Unknown source, ErrorMsg: Unknown description SQL SELECT * FR

Hi

You can't use a where clause for a field that is being calculated in the load script.

You will need something like

Where (MShipQty <> 0 and MBackOrderQty <> 0) Or (LEN(TRIM(MShipQty)) <> 0 and LEN(TRIM(MBackOrderQty)) <> 0) AND LineType = 1.

I prefer to use Len and Trim over isnull incase of random spaces.

Mark

View solution in original post