Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Where Clause

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';

1 Solution

Accepted Solutions
sunny_talwar

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';

View solution in original post

8 Replies
sunny_talwar

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';

Not applicable
Author

Perfect, thank you so much !! 

Not applicable
Author

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 ?

Not applicable
Author

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

sunny_talwar

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.

Not applicable
Author

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.

sunny_talwar

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

Not applicable
Author

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