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: 
Vinil
Contributor III
Contributor III

Rolling 30 days to generate qvd

How to get the rolling 30 days dates in the script.

Can any one please provide the logic.

 

Thanks in Advance.

 

 

vinil
1 Solution

Accepted Solutions
Vegar
MVP
MVP

I'm not really sure if I understand you completely, but I give it at shot.  

//I assume this is your data containing a date field of some kind
MyData:
LOAD id,num(InvoiceDate) as %date,Amount INLINE [
id,InvoiceDate,Amount
1,2019-03-09,78
2,2019-03-10,32
3,2019-03-12,30
4,2019-03-18,57
5,2019-03-06,59
6,2019-03-04,74
7,2019-03-02,30
8,2019-03-13,53
9,2019-03-03,65
10,2019-03-03,60
11,2019-02-13,53
12,2019-02-03,65
13,2019-02-03,60
];


//Assuming you want to create 30 day qvd based on the max date in your MyData table
Calendar30Days:
LOAD 
	%date,
	Date(%date) as Date,
	Year(%date) as Year,
	Month(%date) as Month,
	WeekDay(%date) as Weekday
;
LOAD
	%date - IterNo()-1 as %date
WHILE 
 IterNo()  <= 30 //30 iteration 
;
LOAD 
	MAX(%date) as %date
Resident 
	MyData
;

store Calendar30Days into Calendar30Days.qvd;

View solution in original post

4 Replies
Vegar
MVP
MVP

Could you try to explain what you are trying to do? Do you want to create a qvd table with only 30 dates based date values in another table or is it something else that you want?
Vinil
Contributor III
Contributor III
Author

I want to generate the QVDs for rolling 30 days.

Ex;if today is my current date and i need to get last 29 days including today's date QVD.

vinil
Vegar
MVP
MVP

I'm not really sure if I understand you completely, but I give it at shot.  

//I assume this is your data containing a date field of some kind
MyData:
LOAD id,num(InvoiceDate) as %date,Amount INLINE [
id,InvoiceDate,Amount
1,2019-03-09,78
2,2019-03-10,32
3,2019-03-12,30
4,2019-03-18,57
5,2019-03-06,59
6,2019-03-04,74
7,2019-03-02,30
8,2019-03-13,53
9,2019-03-03,65
10,2019-03-03,60
11,2019-02-13,53
12,2019-02-03,65
13,2019-02-03,60
];


//Assuming you want to create 30 day qvd based on the max date in your MyData table
Calendar30Days:
LOAD 
	%date,
	Date(%date) as Date,
	Year(%date) as Year,
	Month(%date) as Month,
	WeekDay(%date) as Weekday
;
LOAD
	%date - IterNo()-1 as %date
WHILE 
 IterNo()  <= 30 //30 iteration 
;
LOAD 
	MAX(%date) as %date
Resident 
	MyData
;

store Calendar30Days into Calendar30Days.qvd;

Vinil
Contributor III
Contributor III
Author

Thanks,

Vinil

vinil