Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
@sunny_talwar@Gysbert_Wassenaar@swuehl@hic@jagan
I am facing an issue while propagating Month-Year between Start and End Date.
Below is the sample data:
StartDate | EndDate | ID |
10/1/2017 | 11/5/2017 | 1 |
10/15/2017 | 12/20/2017 | 2 |
Output Required:
StartDate | EndDate | ID | Month Year |
10/1/2017 | 11/5/2017 | 1 | Oct-17 |
10/1/2017 | 11/5/2017 | 1 | Nov-17 |
10/15/2017 | 1/20/2018 | 2 | Oct-17 |
10/15/2017 | 1/20/2018 | 2 | Nov-17 |
10/15/2017 | 1/20/2018 | 2 | Dec-17 |
10/15/2017 | 1/20/2018 | 2 | Jan-18 |
Below is the query I am using to propagate Dates. But I want to propagate the same logic on Month-Year level because I can't propagate on Date level due to big data size.
LOAD ID,
Date(StartDate+IterNo()-1) as Date,
StartDate,
EndDate
While StartDate+IterNo()-1 <= EndDate;
LOAD ID,Min(StartDate) as StartDate,Max(EndDate) as EndDate
Inline [
ID,StartDate, EndDate
1,10/1/2017, 11/5/2017
2,10/15/2017, 12/20/2017]
Group By ID;
Thanks,
Ahmed
Try like:
LOAD ID,
MonthName(StartDate+IterNo()-1) as MonthNames,
...
@tresescoThanks for your prompt reply. I have tried it but it didn't work. Can you check on your side and share me your script. Thanks
Solution by @tresesco should work.
Maybe this:
LOAD *, Date(StartDate,'MMM-YY') as [Month Year]
;
LOAD ID,
Date(StartDate+IterNo()-1) as Date,
StartDate,
EndDate
While StartDate+IterNo()-1 <= EndDate;
LOAD ID,Min(StartDate) as StartDate,Max(EndDate) as EndDate
Inline [
ID,StartDate, EndDate
1,10/1/2017, 11/5/2017
2,10/15/2017, 12/20/2017]
Group By ID;
Thanks guys. I have tried the below script and it's giving me the result that I wanted.
SET DateFormat='MM/DD/YYYY';
LOAD ID,MonthName(StartDate,IterNo()-1) as MonthNames,StartDate,EndDate
While MonthName(StartDate,IterNo()-1) <= MonthName(EndDate);
LOAD ID,Min(StartDate) as StartDate,Max(EndDate) as EndDate
Inline [
ID,StartDate, EndDate
1,10/1/2017, 11/5/2017
2,10/15/2017, 12/20/2017]
Group By ID;
PFA