Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
GDI-Paul
Contributor
Contributor

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

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?

Labels (2)
5 Replies
micheledenardi
Specialist II
Specialist II

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
Contributor
Contributor
Author

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
Specialist II
Specialist II

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
Contributor
Contributor
Author

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

Repeat_Rows.JPG

 

Shortened Date Range

Calendar_Table.JPG

 

Thanks again for all the help!

micheledenardi
Specialist II
Specialist II

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;

 

2020-03-10 08_22_21-Qlik Sense Desktop.png