Creating Rows for Every Date between Two Date Fields Pulling Dating from MS SQL Server

[GDI-Paul]

Hello, I need to create a table that has a row for every date between two dates. It is very similar to this , but I need help with the syntax when pulling in data from a MS SQL sever. below is the framework for my script to pull the table. How can I integrate the part that creates the rows for the date?

[Test_Table]:

LIB CONNECT TO 'Microsoft_SQL_Server_test.bi.com';

LOAD JobID,
FirstDay,
LastDay;

[Test_Table]:
SELECT JobID,
FirstDay,
LastDay
FROM "BD_Name".BI."Table_Name" WHERE FirstDate >= '1/1/2014';

Please let me know if there is any additional info I can provide to help?

[micheledenardi]

Try something like this:

Let varMinDate = Num(Peek('FirstDay', 0, 'Test_Table'));
Let varMaxDate = Num(Peek('LastDay', 0, 'Test_Table'));

Calendar:
LOAD
      Date($(varMinDate) + IterNo() - 1) as Date
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

[GDI-Paul]

Thank you so much for your response! Where would I add this to the script? I'm new Qlik and still learning the logic and syntax of it. 

I really appreciate the help!

[micheledenardi]

LIB CONNECT TO 'Microsoft_SQL_Server_test.bi.com';

[Test_Table]:
LOAD JobID,
FirstDay,
LastDay;
SELECT JobID,
FirstDay,
LastDay
FROM "BD_Name".BI."Table_Name" WHERE FirstDate >= '1/1/2014';

Let varMinDate = Num(Peek('FirstDay', 0, 'Test_Table'));
Let varMaxDate = Num(Peek('LastDay', 0, 'Test_Table'));

Calendar:
LOAD
      Date($(varMinDate) + IterNo() - 1) as Date
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

[GDI-Paul]

Thanks!

I think this is getting close, but this has created two tables, is it possible to do it with one?  Also, the calendar table only produced a range for a few dates in 2016, I have records that range from the year 2014-2020. Below is an example of what I'm after.

Desired Results

Shortened Date Range

Thanks again for all the help!

[micheledenardi]

There could be some issues with the the date format, we need to check your data.

Please attach your Qvf with data so we can help you in a better way.

This script works in my case:

Test_Table:
load * Inline [
JobID,StartDay,EndDay
A,01/01/2020,04/01/2020
B,06/01/2020,08/01/2020];


for i=0 to NoOfRows('Test_Table')-1
		Let varMinDate = Num(Peek('StartDay', i, 'Test_Table'));
		Let varMaxDate = Num(Peek('EndDay', i, 'Test_Table'));
        Let varJobID= Peek('JobID', i, 'Test_Table');
        
        Calendar:
		LOAD
        	'$(varJobID)'	as JobID,
      		Date($(varMinDate) + IterNo() - 1) as Date
		AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Next

Left Join(Test_Table)
Load 
	JobID,
    Date
Resident Calendar;
Drop Table Calendar;