topic Re: Problem with condition in Where Clause when using a primary key (Field) subject to an alias in QlikView

Problem with condition in Where Clause when using a primary key (Field) subject to an alias

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have to select only employee that have( EmployID >120) , data is loaded from Access database and this is my load :

Load

EmployID as BusEmployID,// alias

Name,

LastName

SQL SELECT *

FROM Employee;Where BusEmployID > 120;

My problem is : 1/ When I use the alias name (BusEmployID) the load didn't take the condition on considiration and return all the employee even those how have a BusEmployID > 120 ?

2/ When I use the 'EmployID' , it didn't work because this fiels has already changed with the alias.

3/when I delete the alias it work well.

This alias is mondatory for the concatination betwwen tables so I have to work with it .

Did you have explanation for that and how can I resolve it !

Thanks a lot

Re: Problem with condition in Where Clause when using a primary key (Field) subject to an alias

oknotsen — Sun, 05 Jun 2016 09:20:32 GMT

1: You do not use the ALIAS name.

2: You apparently did not test it or pulled the wrong conclusion as the name change takes place in the preceding load (in other words, after the data is pulled in memory). So you should use EmployID.

What mainly goes wrong in your piece of script is the 1 too many semi-colon:

FROM Employee;Where ...

Re: Problem with condition in Where Clause when using a primary key (Field) subject to an alias

Sun, 05 Jun 2016 09:33:55 GMT

sorry it was a wrong typing but my load is

Load

EmployID as BusEmployID,// alias

Name,

LastName

SQL SELECT *

FROM Employee

Where BusEmployID > 120 ;

I tested these three precedent cases , is worked well only when I tested without alias (case 3 ) .

Re: Problem with condition in Where Clause when using a primary key (Field) subject to an alias

Kushal_Chawda — Sun, 05 Jun 2016 11:42:47 GMT

try this

load :

Load

EmployID as BusEmployID,// alias

Name,

LastName

SQL SELECT *

FROM Employee;Where EmployID> 120;

As you are doing preceding load, first SQL statement executes, so BusEmployID in Where clause will not work and it will throw the error. So you should use actual Fieldname from SQL table which is EmployID. Then using preceding load you can give alias to EmployID

Re: Problem with condition in Where Clause when using a primary key (Field) subject to an alias

Sun, 05 Jun 2016 13:02:41 GMT

I Tested it but it returned all the EmployID (it Didn't take in consideration the condition) ???

Re: Problem with condition in Where Clause when using a primary key (Field) subject to an alias

karthickv — Sun, 05 Jun 2016 13:13:50 GMT

You need to use EmployID in your where condition as like below.

Load

EmployID as BusEmployID,

Name,

LastName

SQL SELECT *

FROM Employee Where EmployID > 120;

Also like mentioned by Onno van, I suspect your issue is with multiple semicolon only (as you mentioned in #2 that u have tried using EmployID.).

Re: Problem with condition in Where Clause when using a primary key (Field) subject to an alias

oknotsen — Sun, 05 Jun 2016 15:28:09 GMT

Still the same comment as I did on your initial code:

You apparently did not test it or pulled the wrong conclusion as the name change takes place in the preceding load (in other words, after the data is pulled in memory). So you should use EmployID.

Or in different words:

BusEmployID does not exist until AFTER this complete piece of code is finished, so you can NOT user it in the where clause. You need to use the initial field name, in this case EmployID.

Re: Problem with condition in Where Clause when using a primary key (Field) subject to an alias

Sun, 05 Jun 2016 15:46:38 GMT

this is the resalt with the 'EmployID' as you see the system return 1,2,3 ?

Re: Problem with condition in Where Clause when using a primary key (Field) subject to an alias

Sun, 05 Jun 2016 15:49:55 GMT

Yes it was a wrong typing ,

and for this

Load

EmployID as BusEmployID,

Name,

LastName

SQL SELECT *

FROM Employee Where EmployID > 120;

it return this result and as you see 1,2,

3,4.... shouldn't be exist,

Re: Problem with condition in Where Clause when using a primary key (Field) subject to an alias

oknotsen — Sun, 05 Jun 2016 15:51:50 GMT

I suggest you do some research about that column. I think it contains spaces or other (not visible) symbols. The aligning of the values shows that they are not all considered "numbers".

See if you can load the field like this:

'|' & EmployID & '|' as BusEmployID

Analyze the field after that.

Re: Problem with condition in Where Clause when using a primary key (Field) subject to an alias

oknotsen — Sun, 05 Jun 2016 15:57:25 GMT

I suggest you do some research about that column. I think it contains spaces or other (not visible) symbols. The aligning of the values shows that they are not all considered "numbers".

See if you can load the field like this:

'|' & EmployID & '|' as BusEmployID

Analyze the field after that.

Re: Problem with condition in Where Clause when using a primary key (Field) subject to an alias

Sun, 05 Jun 2016 16:06:34 GMT

I notify that and I verify the type in the load and it is an Integer I try even to cast it with Num() function but it didn't return any thing ????