Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have a table in the below format from SQLwhich have Start and End dates to dvise of a given Return.
I need to generate a row for each of the Metrics for each Month between the From and To dates - returning the correct Return Value.
I have tried various loops, iterno() while script and just cannot get it to work correctly with the start and end date fields.
I have uploaded a QV for manipulation - Please can you have a look at the attached app and help :-)) Many thanks in advance
Start Table
MetricID | From | To | Returns |
1 | 01/01/2013 | 01/03/2013 | 1 |
1 | 01/04/2014 | 01/06/2014 | 2 |
2 | 01/01/2013 | 01/03/2013 | 3 |
2 | 01/04/2014 | 01/07/2014 | 4 |
Tring to get to this
MetricID | From | To | Returns |
1 | 01/01/2013 | 01/01/2013 | 1 |
1 | 01/02/2013 | 01/02/2013 | 1 |
1 | 01/03/2013 | 01/03/2013 | 1 |
1 | 01/04/2013 | 01/04/2013 | 2 |
1 | 01/05/2013 | 01/05/2013 | 2 |
1 | 01/06/2013 | 01/06/2013 | 2 |
2 | 01/01/2013 | 01/01/2013 | 3 |
2 | 01/02/2013 | 01/02/2013 | 3 |
2 | 01/03/2013 | 01/03/2013 | 3 |
2 | 01/04/2013 | 01/04/2013 | 4 |
2 | 01/05/2013 | 01/05/2013 | 4 |
2 | 01/06/2013 | 01/06/2013 | 4 |
2 | 01/07/2013 | 01/07/2013 | 4 |
!
TEST:
LOAD * INLINE
[
MetricID, From, To, Returns
1, 01/01/2013, 01/03/2013, 1
1, 01/04/2014, 01/06/2014, 2
2, 01/01/2013, 01/03/2013, 3
2, 01/04/2014, 01/07/2014, 4
];
NoConcatenate
Final:
LOAD
MetricID,
Date(AddMonths(From,IterNo()-1)) as From,
Date(AddMonths(From,IterNo()-1)) as To,
Returns
Resident TEST
While AddMonths(From,IterNo()-1) <= To;
Drop Table TEST;
TEST:
LOAD * INLINE
[
MetricID, From, To, Returns
1, 01/01/2013, 01/03/2013, 1
1, 01/04/2014, 01/06/2014, 2
2, 01/01/2013, 01/03/2013, 3
2, 01/04/2014, 01/07/2014, 4
];
NoConcatenate
Final:
LOAD
MetricID,
Date(AddMonths(From,IterNo()-1)) as From,
Date(AddMonths(From,IterNo()-1)) as To,
Returns
Resident TEST
While AddMonths(From,IterNo()-1) <= To;
Drop Table TEST;
Hi Joanne,
I usually solve this kind of problem with a Master Calendar table. In the Script below you only need to replace YOURTABLE with the name of your previously loaded table and DATE with the Name of the Date field in YOURTABLE. Also replace DATE in the MasterCalendar table below with the same name, so you get a connection between YOURTABLE and MasterCalendar.
You can erase all date formats you do not need for your purpose from the MasterCalendar table.
//calculate Min and Max Date
MinMaxDate:
Load
Max(DATE) as MaxDate,
Min(DATE) as MinDate
Resident YOURTABLE;
//Define Vaiables fpr min and max date
Let vMaxDateDate = date(peek('MaxDate',0,'MinMaxDate'));
Let vMinDate = num(peek('MinDate',0,'MinMaxDate'));
Let vMaxDate = num(peek('MaxDate',0,'MinMaxDate'));
Let vToday = $(vMaxDate);
//Drop not used table
Drop Table MinMaxDate;
//generate dates between min and max date
TempCal:
Load
date($(vMinDate) + RowNo() - 1) as TempDate
AutoGenerate $(vMaxDate) - $(vMinDate) + 1;
MasterCalendar:
LOAD
TempDate AS DATE,
TempDate AS FullDate,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear, //Not the real WeekYear
inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag, //For Filter Purpose, is the date in the current year and before today
inyeartodate(TempDate, $(vToday), -1) * -1 AS LastYTDFlag //For Filter Purpose, is the date in the last year and before today
RESIDENT TempCal
ORDER BY TempDate ASC;
DROP TABLE TempCal;
DATA:
load MetricID,
Date(AddMonths(From,IterNo()-1)) as From,
Date(AddMonths(From,IterNo()-1)) as To,
Returns
while AddMonths(From,IterNo()-1) <= To;
LOAD MetricID, Date#(From,'DD/MM/YYYY') as From, Date#(To, 'DD/MM/YYYY')as To, Returns INLINE
[MetricID, From, To, Returns
1,01/01/2013,01/03/2013,1
1,01/04/2014,01/06/2014,2
2,01/01/2013,01/03/2013,3
2,01/04/2014,01/07/2014,4
];
Also look here: How to populate a sparsely populated field
DATA:
Load MetricID,
Date(From+IterNo()-1) as From,
Date(From+IterNo()-1) as To,
Returns
While From +IterNo()-1<= To;
LOAD * INLINE
[MetricID, From, To, Returns
1, 01/01/2013, 01/03/2013, 1
1, 01/04/2013, 01/06/2013, 2
2, 01/01/2013, 01/03/2013, 3
2, 01/04/2013, 01/07/2013, 4];
PFA
I guess there is a little typo inyour sample data. Corrected and tried.
Thnankyou very much this worked perfectly
Thankyou I was working along these lines orignially 🙂
Thankyou everyone - Problem has been solved!
Guess I really need to get my head around the iterno() function - so I can correctly understand how to use it and when.
Many Thanks
be careful, Manish's solution considers your date format as DD/MM/YYYY, while I considered your date fommat as MM/DD/YYYY. Two would generate different dates, though would look similar.