Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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...
;
yeah thats what I figured but these types of sql doesnt seem to work in qlikview for some reason
Should work absolutely fine I think? Will test
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