Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi folks,
I've got a QVD (attached) with a start and end date. I need to create a new field as per Obligation_Period below using these two fields, so a YYYY-MMM of the start date, and a YYYY-MMM of the month before the end date, and all the months between. All concatenated with a nice delimiter such as ; in the same field
i.e.
Any ideas? I cannot just do a start +1 month, start +2 month etc because the start and end ranges may vary in length.
Try this
Table:
LOAD start,
end,
due,
status,
received,
periodKey,
Concat(Obligation_Period, ';', Obligation_Period) as Obligation_Period
Group By start, end, due, status, received, periodKey;;
LOAD start,
end,
due,
status,
received,
periodKey,
Date(MonthStart(start, IterNo() - 1), 'YYYY-MMM') as Obligation_Period
FROM [..\..\Downloads\obligations.qvd] (qvd)
While MonthStart(start, IterNo() - 1) < MonthStart(end);
Try this
Table:
LOAD start,
end,
due,
status,
received,
periodKey,
Concat(Obligation_Period, ';', Obligation_Period) as Obligation_Period
Group By start, end, due, status, received, periodKey;;
LOAD start,
end,
due,
status,
received,
periodKey,
Date(MonthStart(start, IterNo() - 1), 'YYYY-MMM') as Obligation_Period
FROM [..\..\Downloads\obligations.qvd] (qvd)
While MonthStart(start, IterNo() - 1) < MonthStart(end);
Awesome! Thanks Sunny!