topic Re: Qlik SQL Where WEEK Statement in QlikView

Qlik SQL Where WEEK Statement

mr_lampert — Sat, 16 Nov 2024 03:58:53 GMT

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

Re: Qlik SQL Where WEEK Statement

tresB — Tue, 02 Apr 2019 12:52:47 GMT

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

Re: Qlik SQL Where WEEK Statement

vvira1316 — Tue, 02 Apr 2019 13:03:53 GMT

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.

Re: Qlik SQL Where WEEK Statement

mr_lampert — Tue, 02 Apr 2019 13:04:39 GMT

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!