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

Previous businessday in ETL stage

Hi,

I have some daily QVDs where I just want data from previous businessday how can I in the ETL stage only get that?

I currently pick data between getdate()-2 and getdate()-1 Which on monday gives me issues.

5 Replies
adamdavi3s
Master
Master

Hi Niklas,

Can't you make your getdate() -2 and getdate()-1 a bit cleverer so something like

WHERE DATE  BETWEEN CASE

WHEN DATEPART(WEEKDAY, getdate()) = 2  THEN  getdate() -4 ELSE getdate() -2 END AND 

CASE

WHEN DATEPART(WEEKDAY, getdate()) = 2  THEN  getdate() -3 ELSE getdate() -1 END

Gysbert_Wassenaar

You can use the FirstWorkDate function to retrieve a prior businessday:

LOAD

     ...some fields....,

     MyDateField,

     FirstWorkDate( MyDateField, 1) as PreviousBusinessDay,

     ...some other fields...

FROM

     ...source data...

     ;


talk is cheap, supply exceeds demand
Not applicable
Author

yeah thats what I figured but these types of sql doesnt seem to work in qlikview for some reason

adamdavi3s
Master
Master

Should work absolutely fine I think? Will test

adamdavi3s
Master
Master

Works fine here with my Database (just randomly picked a server, db and table)

ODBC CONNECT32 TO BLAH;

LOAD *;

SQL

USE [DBBLAH]

SELECT

      ,[Installed_Date]

    

  FROM [semantic].[vw_Date_Installed]

WHERE [Installed_Date]  BETWEEN CASE

WHEN DATEPART(WEEKDAY, getdate()) = 2  THEN  getdate() -4 ELSE getdate() -2 END AND

CASE

WHEN DATEPART(WEEKDAY, getdate()) = 2  THEN  getdate() -3 ELSE getdate() -1 END