Using With Clause in Load Statement

Stefan_Weber — Thu, 14 Feb 2019 14:48:20 GMT

Hello Qlikview Community,

I hope I am not bothering you with all those questions, but I got one more:

I am trying to Load the following statement:

SQL

DECLARE @fromDate DATE = '2008-01-01',
DECLARE @toDate DATE = convert(nvarchar(10),DATEADD(Day, -1, Dateadd(Year, Datediff(Year,0,Getdate())+1,0)), 120),
WITH dates AS(
SELECT [ProductionDate] = @fromDate,
[Year] = DATEPART(year,@fromDate),
[Month] = DATEPART(month,@fromDate),
[Week] = DATEPART(week,@fromDate),
[Day] = DATEPART(day, @fromDate),
[YearMonth] = CONVERT(nvarchar(7),@fromDate,120),
[YearMonthExclude] = CONVERT(nvarchar(7),@fromDate,120)

UNION ALL

SELECT [ProductionDate] = DATEADD(DAY, 1, [ProductionDate]),
[Year] = DATEPART(year,DATEADD(DAY, 1, [ProductionDate])),
[Month] = DATEPART(month,DATEADD(DAY, 1, [ProductionDate])),
[Week] = DATEPART(week,DATEADD(DAY, 1, [ProductionDate])),
[Day] = DATEPART(day, DATEADD(DAY, 1, [ProductionDate])),
[YearMonth] = CONVERT(nvarchar(7),DATEADD(DAY, 1, [ProductionDate]),120),
[YearMonthExclude] = CONVERT(nvarchar(7),DATEADD(DAY, 1, [ProductionDate]),120)

FROM dates WHERE DATEADD(DAY, 1, [ProductionDate]) <= @toDate
)

SELECT [ProductionDate],
[Year],
[Month],
[Week],
[Day],
[YearMonth],
[YearMonthExclude]
FROM dates OPTION (maxrecursion 10000);

Unfortunately With requires a semicolon which terminates the SQL statement.

Does anyone of you have an idea how I could get the required results.

(Dates from 01.01.2018 until the end of the current year seperated in Day, Week, Month etc)

Without using the With statement?

Appreciate your support.

Stefan

Re: Using With Clause in Load Statement

Stefan_Weber — Fri, 15 Feb 2019 10:32:27 GMT

Hello,

I solved it for this case by using the following statement.

LET vStartDate = DATE(MakeDate(2008, 01, 01),'YYYY-MM-DD');
LET vEndDate = DATE(MakeDate(YEAR(NOW()), 12, 31), 'YYYY-MM-DD');

DO

LET vProductionDate = DATE('$(vStartDate)','YYYY-MM-DD');
LET vYear = NUM(YEAR('$(vStartDate)'));
LET vMonth = NUM(MONTH('$(vStartDate)'),00);
LET vWeek = NUM(WEEK('$(vStartDate)'));
LET vDay = NUM(DAY('$(vStartDate)'),00);
LET vYearMonth = '$(vYear)' & '$(vMonth)';
LET vYearMonthExclude = '$(vYear)' & '$(vMonth)';

PRODUCTION_DATES:
Load * inline [ProductionDate, Year, Month, Week, Day, YearMonth, YearMonthExclude

$(vProductionDate), $(vYear), $(vMonth), $(vWeek), $(vDay), $(vYearMonth), $(vYearMonthExclude)];

LET vStartDate = DATE('$(vStartDate)' + 1,'YYYY-MM-DD');

LOOP WHILE '$(vStartDate)' <= '$(vEndDate)'

Regards

Stefan

topic Re: Using With Clause in Load Statement in QlikView

Using With Clause in Load Statement

Re: Using With Clause in Load Statement