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: 
mr_lampert
Contributor III
Contributor III

Qlik SQL Where WEEK Statement

Hello Community,

in my script the LOAD-Command filters with a Where-Statement for a calender week. But is there a way to move the Where-Statement into the SQL-Statement? My goal is to shorten loading time, because the Where-Statement in the in the LOAD-Command takes a lot of time.

LOAD Distinct
    WEEK(TIME_NEW) AS WEEK_LOAD
WHERE WEEK(TIME_NEW) = 14 //Remove this to the SQL-Statement ; SQL SELECT TIME_NEW FROM IN.TEST WHERE WEEK(TIME_NEW) = 14 //But unfortunately that does not work ;

Thanks and see you soon

David Cat Happy

Labels (4)
1 Solution

Accepted Solutions
mr_lampert
Contributor III
Contributor III
Author

Unfortunately not, but you gave me an idea! Because we work with oracle and this works great:

TO_CHAR(TO_DATE(TIME_NEU, 'dd.mm.yyyy'), 'WW') = 14 

Thanks for the Inspiration! Heart

View solution in original post

3 Replies
tresesco
MVP
MVP

If your db is sql server, you can try using datepart(), like:

 

SQL SELECT 
    TIME_NEW
FROM IN.TEST
WHERE DatePart(week, TIME_NEW) = 14
vvira1316
Specialist II
Specialist II

May be you can load all rows first and in field you use week along with num  (just to make sure week is giving numeric value) functions to get numeric week value from the time field. Then use this intermediate table to load final table where you are only loading rows with a particular value. I think that may help in performance.

intermediatetable:

LOAD Distinct
WEEK(TIME_NEW) AS WEEK_LOAD;

finaltable:  

LOAD                                                //distinct may not be needed here as distinct in intermediate table would take care of it

                                                          //or you can remove from there and use it here as per your need
WEEK_LOAD
resident intermediatetable
where WEEK_LOAD = 14;

drop table intermediatetable;

 

I hope this helps. You may also want to check/seek online SQL help. 

mr_lampert
Contributor III
Contributor III
Author

Unfortunately not, but you gave me an idea! Because we work with oracle and this works great:

TO_CHAR(TO_DATE(TIME_NEU, 'dd.mm.yyyy'), 'WW') = 14 

Thanks for the Inspiration! Heart