topic Issue while using the WHERE clause in LOAD Statement! in QlikView

Issue while using the WHERE clause in LOAD Statement!

Fri, 09 Apr 2010 12:33:57 GMT

Hi All,

I am facing problem in using WHERE clause in a LOAD statement. I am getting the below error while using this LOAD statement. Hope some one can help me out.


SecurityTable:
LOAD DISTINCT
 'USER' as [ACCESS],
 upper([userid]) as [USERID],
 upper([password]) as [PASSWORD],
 upper(mfaccode) as SecurityMfa,
 'Some' as SecurityPage
resident TempTab where userid not in('001001admin','001001vadmin');

CONCATENATE (SecurityTable)
LOAD DISTINCT
 'USER' as [ACCESS],
 upper([userid]) as [USERID],
 upper([password]) as [PASSWORD],
 upper(mfaccode) as SecurityAccount,
 '*' as SecurityMfa,
 'Some' as SecurityPage
resident TempTab where userid in('001001admin','001001vadmin');

Error:


Garbage after statement
SecurityTable:
LOAD DISTINCT
 'USER' as [ACCESS],
 upper([userid]) as [USERID],
 upper([password]) as [PASSWORD],
 upper(mfaccode) as SecurityMfa,
 'Some' as SecurityPage
resident TempTab where userid not in('001001admin','001001vadmin')

Issue while using the WHERE clause in LOAD Statement!

Fri, 09 Apr 2010 12:55:57 GMT

I have had problems with in before. Multiple values do not seem to be allowed.

A quick workaround is

where userid not ('001001admin') and userid not ('001001vadmin')

or where userid <>('001001admin') and userid <> ('001001vadmin')

I am sure there is a smarter solution though!

Issue while using the WHERE clause in LOAD Statement!

Fri, 09 Apr 2010 13:03:01 GMT

Thanks for very quick reply!


rickl wrote:
I have had problems with in before. Multiple values do not seem to be allowed.
A quick workaround is
where userid not ('001001admin') and userid not ('001001vadmin')
or where userid <>('001001admin') and userid <> ('001001vadmin')
I am sure there is a smarter solution though!<div></div>

For this

[code[where userid not in('001001admin','001001vadmin')

This I will put like this

where userid not ('001001admin') and userid not ('001001vadmin')
or where userid <>('001001admin') and userid <> ('001001vadmin')

How about for below one:

where userid in('001001admin','001001vadmin')

Thanks and Regards,

Rikab

Issue while using the WHERE clause in LOAD Statement!

Fri, 09 Apr 2010 13:06:04 GMT

Do the same, but with an "OR" statement:

[CODE]

where userid in "a" or userid in "b"

[CODE\]

Issue while using the WHERE clause in LOAD Statement!

skaredovs — Fri, 09 Apr 2010 13:13:30 GMT

Hi,

I suggest to use function MATCH

where match(userid,'001001admin','001001vadmin')=0

Issue while using the WHERE clause in LOAD Statement!

Fri, 09 Apr 2010 13:14:19 GMT


rickl wrote:
Do the same, but with an "OR" statement:
<blockquote><pre>
where userid in "a" or userid in "b"
[CODE\]<div></div>

It has accepted <> instead of not in. But it is not accepting in(or). Throwing the below error. Let me know what to do.

Garbage after statement
CONCATENATE (SecurityTable)
LOAD DISTINCT
 'USER' as [ACCESS],
 upper([userid]) as [USERID],
 upper([password]) as [PASSWORD],
 upper(mfaccode) as SecurityAccount,
 '*' as SecurityMfa,
 'Some' as SecurityPage
resident TempTab where userid in ('001001admin') or userid in ('001001vadmin')

Script that has been used:


SecurityTable:
LOAD DISTINCT
 'USER' as [ACCESS],
 upper([userid]) as [USERID],
 upper([password]) as [PASSWORD],
 upper(mfaccode) as SecurityMfa,
 'Some' as SecurityPage
resident TempTab where userid <> ('001001admin') and userid <> ('001001vadmin');

CONCATENATE (SecurityTable)
LOAD DISTINCT
 'USER' as [ACCESS],
 upper([userid]) as [USERID],
 upper([password]) as [PASSWORD],
 upper(mfaccode) as SecurityAccount,
 '*' as SecurityMfa,
 'Some' as SecurityPage
resident TempTab where userid in ('001001admin') or userid in ('001001vadmin');

Thanks and Regards,

Rikab

Issue while using the WHERE clause in LOAD Statement!

Fri, 09 Apr 2010 13:28:24 GMT

It has accepted LIKE instead of IN. But this login script is not working you have any idea why? By the way thanks a lot for your effort.

LOAD DISTINCT
 'USER' as [ACCESS],
 upper([userid]) as [USERID],
 upper([password]) as [PASSWORD],
 upper(mfaccode) as SecurityAccount,
 '*' as SecurityMfa,
 'Some' as SecurityPage
resident TempTab where [userid] like ('001001admin') or userid like ('001001vadmin');

Thanks and Regards,

Rikab

Issue while using the WHERE clause in LOAD Statement!

Fri, 09 Apr 2010 13:38:15 GMT

Try "=" instead of "in"

Issue while using the WHERE clause in LOAD Statement!

Fri, 09 Apr 2010 13:51:02 GMT


rickl wrote:
Try "=" instead of "in"<div></div>

Yes, both "=" and LIKE works. Data has been loaded now. But I think there is some thing wrong with the section access script.

Thanks and Regards,

Rikab

Issue while using the WHERE clause in LOAD Statement!

boorgura — Fri, 09 Apr 2010 15:49:27 GMT

I might be wrong, but just noticed that in the initial code, there in no space after IN.

Please check if that is carusing the error.

Would be great if you let me know, whether that was the issue.

Issue while using the WHERE clause in LOAD Statement!

Mon, 12 Apr 2010 05:13:31 GMT

Hello Rocky!

Thanks for your reply. Let me know which one you are talking about. There are many codes above.

Thanks and Regards,

Rikab

Issue while using the WHERE clause in LOAD Statement!

Mon, 26 Apr 2010 13:51:08 GMT

I have had the same problem using WHERE field IN (x,y,z) and have used the above MATCH solution which seems to work well.

However, it's very frustrating because WHERE field IN (x,y,z) seems to work when connecting to source data via ODBC but doesn't work when pointing to a .qvd file.