topic Re: Where Clause in QlikView

Where Clause

Tue, 22 Nov 2016 13:52:24 GMT

I am using the following simple where clause to filter records after they are loaded from an excel file called Active.xlsx. The where clause does not work, how can I fix the syntax in the script? I've attached the supporting documents.

WHERE [Entity Status]='A';

Re: Where Clause

sunny_talwar — Tue, 22 Nov 2016 14:08:35 GMT

Removed a extra semi colon. Try this:

T1_Active:

LOAD [Entity Status],

[Policy Type],

[Profession Flag],

[Customer No],

[Account No],

[Original Eff Date],

[Policy Eff Date],

[Termin Date],

[Student Flag],

[Source Code],

[State Code],

[Base Premium],

'A' as Entity_Status2,

if([Student Flag]='Y','STUD','PROF')as Studflag,

Floor((Interval(Today()-Date#([Original Eff Date],'YYYYMMDD'),'D'))/365) as Yrsins1

FROM

Active.xlsx

(ooxml, embedded labels)

WHERE [Entity Status]='A';

Re: Where Clause

Tue, 22 Nov 2016 14:12:44 GMT

Perfect, thank you so much !!

Re: Where Clause

Tue, 22 Nov 2016 14:17:20 GMT

You already resolved my question, but I have a related issue. When I modify this script to directly read the data via an ODBC connection to a file that contains 6 million records, the load takes 60 minutes. If I do the same thing in an access database via an ODBC connection the load takes only 5 seconds. I can I access the data faster ?

Re: Where Clause

Tue, 22 Nov 2016 14:23:24 GMT

I had a typo in prior message, how can I access the data faster than 60 minutes.

Re: Where Clause

sunny_talwar — Tue, 22 Nov 2016 14:27:57 GMT

Is ths access database local and the file sitting at a server which is sort of away from your machine? The speed of pulling the data also depends on how far the data is sitting.

Re: Where Clause

Tue, 22 Nov 2016 15:04:19 GMT

The process is the same for both MS Access database and Qlikview document. The MS Access database is on my local C:\ drive and so is the Qlikview document. The data resides on a server in Chicago Illionois, my local desktop is in Philadelphia Pennsylvania. The ODBC connection in the MS Access database reads the data in 1 second, I can view all 6 million records in 1 second. The ODBC connection in Qlikview loads the records in 60 minutes.

Re: Where Clause

sunny_talwar — Wed, 23 Nov 2016 01:35:58 GMT

I am not an expert when it comes to connection with databases. I think it might be helpful for you to create another thread for this issue so that people with expertise in that area can help you better.

Best,

Sunny

Re: Where Clause

Wed, 23 Nov 2016 13:47:52 GMT

Thanks Sunny T, I appreciate your help and direction. You have a fantastic day !!