Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a start date and it is in the format 'MMM-YY' I want to be able to display every month it has been open for until its close date which is in the same format 'MMM-YY'. And if it dose not have a close date display every month including the current month as of today.
The data should look like the below if possible:
ID Start Date Close Date Opened
1 Aug-18 Oct-18 Sep-18
2 Jun-18 Oct-18 Jul-18,Aug-18,Sep-18
3 Jul-18 - Aug-18,Sep-18,Oct-18
Any help would be great thanks.
Hi Ruth. I think it may be like this
Table: LOAD ID, [Start Date], [Close Date],
Date(Date#([Start Date], 'MMM-YY')) as [StartDate],
Date(If(Len([Close Date])=0, MonthStart(Today(), 1), Date#([Close Date], 'MMM-YY'))) as [EndDate];
LOAD * INLINE [
ID, Start Date, Close Date
1, Aug-18, Oct-18
2, Jun-18, Oct-18
3 , Jul-18,
];
Temp_1: LOAD Min([StartDate]) as MinDate Resident Table; vMinDate = Num(Peek('MinDate', 0, 'Temp_1'));
Temp_2: LOAD MonthStart($(vMinDate), IterNo() - 1) as [Date] AutoGenerate 1 While MonthStart($(vMinDate), IterNo() - 1) <= MonthStart(Today(), 1);
Inner Join(Temp_2) IntervalMatch (Date) LOAD Distinct [StartDate], [EndDate] Resident [Table];
Temp_3: LOAD [StartDate], [EndDate], Concat(Date([Date], 'MMM-YY'), ', ', Date) as [Opened] Resident Temp_2 Where not Match([Date], [StartDate], [EndDate]) Group By [StartDate], [EndDate];
Left Join(Table) LOAD * Resident Temp_3; DROP Tables Temp_1, Temp_2, Temp_3; DROP Fields [StartDate], [EndDate];
Check the below link.
Hi Ruth. I think it may be like this
Table: LOAD ID, [Start Date], [Close Date],
Date(Date#([Start Date], 'MMM-YY')) as [StartDate],
Date(If(Len([Close Date])=0, MonthStart(Today(), 1), Date#([Close Date], 'MMM-YY'))) as [EndDate];
LOAD * INLINE [
ID, Start Date, Close Date
1, Aug-18, Oct-18
2, Jun-18, Oct-18
3 , Jul-18,
];
Temp_1: LOAD Min([StartDate]) as MinDate Resident Table; vMinDate = Num(Peek('MinDate', 0, 'Temp_1'));
Temp_2: LOAD MonthStart($(vMinDate), IterNo() - 1) as [Date] AutoGenerate 1 While MonthStart($(vMinDate), IterNo() - 1) <= MonthStart(Today(), 1);
Inner Join(Temp_2) IntervalMatch (Date) LOAD Distinct [StartDate], [EndDate] Resident [Table];
Temp_3: LOAD [StartDate], [EndDate], Concat(Date([Date], 'MMM-YY'), ', ', Date) as [Opened] Resident Temp_2 Where not Match([Date], [StartDate], [EndDate]) Group By [StartDate], [EndDate];
Left Join(Table) LOAD * Resident Temp_3; DROP Tables Temp_1, Temp_2, Temp_3; DROP Fields [StartDate], [EndDate];
Hi Andrey this worked perfectly. Thank you very much.
You're welcome)