Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
have a table with the following month close dates
8/28/2017
9/21/2017
10/28/2017
need to add a new column with all dates in between so I can see all dates related to the month close dates as follow
8/28/2017 8/1/2017
8/28/2017 8/2/2017
8/28/2017 8/3/2017
...
8/28/2017 8/28/2017
9/21/2017 8/29/2017
9/21/2017 8/30/2017
9/21/2017 8/31/2017
9/21/2017 9/1/2017
...
9/21/2017 9/21/2017
and so on..
any quick solution you can share?
Like this?
SET DateFormat='M/D/YYYY';
LOAD FieldName, MonthStart, Date(MonthStart + IterNo() - 1) as Date
While Date(MonthStart + IterNo() - 1) <= FieldName;
LOAD *, MonthStart(FieldName) as MonthStart Inline [
FieldName
8/28/2017
9/21/2017
10/28/2017
];
almost right but it is not. We dont need to use month start. the first date here on my sample happen to be a month start but if you look at the dates between august and September.. month start doesnt work. let me know if you know how to adjust the logic
Not sure, I followed. Can you make output for one date to test
if you take this example. if you notice when the first column value is 9/21/2017, the second column shows next value (8/29/2017) which is next value after 8/28/2017 showing above 9/21/2017
8/28/2017 8/28/2017
9/21/2017 8/29/2017
9/21/2017 8/30/2017
9/21/2017 8/31/2017
9/21/2017 9/1/2017
...
9/21/2017 9/21/2017
Still not sure, When i say i am not understand your output. You shouldn't copy paste from previous one. I would request you to share sample output in excel from given data..
the sample I provided is very clear.. sorry you cannot understand it and thank you or your input. will wait for others to take a look.
thanks again.
Yes, We will wait for other reply. You are not provided answer for 10/28/2017
Are you looking for this? It captures from providing data as 8,9,10 months whole data..
LOAD FieldName, MonthStart, Date(MonthStart + IterNo() - 1) as Date
While Date(MonthStart + IterNo() - 1) <= MonthEnd(FieldName);
How about interval matching the date range from previous month close(+1) to month close:
// Load the month close dates
MonthCloses:
LOAD Date(Date#(MonthClose, 'M/dd/yyyy')) as MonthClose
INLINE
[
MonthClose
8/28/2017
9/21/2017
10/28/2017
];
// Create a match table for the month ranges
Match:
LOAD Alt(Previous(MonthClose) + 1, 0) as FromDate,
MonthClose as ToDate
Resident MonthCloses;
Fact: // load your fact table with the date field....
LOAD
...
Date,
...
;
// Apply the month table to the fact
Left Join(Fact)
IntervalMatch(Date)
LOAD FromDate, ToDate as CloseDate
Resident Match;
// Cleanup
DROP Field FromDate;
DROP Tables MonthCloses, Match;