Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How to get the rolling 30 days dates in the script.
Can any one please provide the logic.
Thanks in Advance.
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;
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.
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;
Thanks,
Vinil