- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to generate a series of Months?
I have a row of data like the screenshot below
So the idea is that based on the Frequency and the Start accruing fields I should be able to generate a sequence of Month-Year and then assign the Monthly values to them.Can someone please help me achieve this?
Thanks
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Here is another way to do this (and probably a little simpler)
Table:
LOAD Code,
Dept,
Monthly,
Description,
MonthName(AddMonths([Start Accruing], IterNo() - 1)) as [Start Accruing]
While IterNo() <= FrequencyNum;
LOAD Pick(Match(Frequency, 'Quarterly', '6 Monthly', 'Annually'), 3, 6, 12) as FrequencyNum,
MonthName(Date#([Start Accruing], 'MMM-YY')) as [Start Accruing],
Frequency,
Code,
Dept,
Total,
Monthly,
Description,
Periodcovered,
PP;
LOAD * Inline [
Code, Dept, Total, Monthly, Description, Periodcovered, Frequency, Start Accruing, PP
7455, 561, 4507.80, 375.65, Mainel - Telephone maintenance, Jan14-Dec14, Annually, Jan-15, 876
];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='D/MM/YYYY';
SET TimestampFormat='D/MM/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
temptable:
Load [Start Accruing] as Dt, *;
load * inline
[
Code, Dept, Total, Monthly,Description, Periodcovered, Frequency, Start Accruing, PP
7455, 561, 4507.80, 375.65, Mainel - Telephone maintenance, Jan14-Dec14, Annually, Jan-15, 876
];
Let StartMonth=Date#(Peek('Dt',-1,'temptable'),'MMM-YY');
Let vCode=Date#(Peek('Code',-1,'temptable'),'MMM-YY');
Let vDept=Date#(Peek('Dept',-1,'temptable'),'MMM-YY');
Let vTotal=Date#(Peek('Total',-1,'temptable'),'MMM-YY');
Let vMonthly=Date#(Peek('Monthly',-1,'temptable'),'MMM-YY');
Let vDescription=Date#(Peek('Description',-1,'temptable'),'MMM-YY');
Let vPeriodcovered=Date#(Peek('Periodcovered',-1,'temptable'),'MMM-YY');
Let vFrequency=Date#(Peek('Frequency',-1,'temptable'),'MMM-YY');
Let vPP=Date#(Peek('PP',-1,'temptable'),'MMM-YY');
trace '$(StartMonth)';
LEt i = 1;
do while i < 12
Let m = Date(AddMonths( StartMonth, $(i)), 'MMM-YY');
temptable1:
Load '$(vCode)' as Code,'$(vDept)' as Dept, '$(vTotal)' as Total, '$(vMonthly)' as Monthly,'$(vDescription)' as Description, '$(vPeriodcovered)' as Periodcovered, '$(Frequency)' as Frequency, '$(m)' as Dt, '$(vPP)' as PP
autogenerate 1;
Trace $(i);
Let i = $(i) + 1;
loop;
Concatenate(temptable)
load * resident temptable1;
drop table temptable1;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How do you know the this needs to be until Dec-2015? Always 1 year?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Sunny T
Good question!No it's not always 12 months. In the original table I have the information for this. If I divide Total by Monthly I get the number of months for which I should populate monthly payments. The frequency field also tells me how many months is needed. For instance, annually means 12 months , quarterly is 3, 6 monthly 6, and monthly 12.
Does that make sense?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@RamKumar Ramagopalan
Thanks for your solution, sounds promising! I'll try it and let you know if it generates the fields I need.
Thanks again
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@RamKumar Ramagopalan
Thanks for the solution, it generates the months the way I intend to do. How can I expand it so that instead of hard coding 12 months, the number of frequency will be used?
Many thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Here is another way to do this (and probably a little simpler)
Table:
LOAD Code,
Dept,
Monthly,
Description,
MonthName(AddMonths([Start Accruing], IterNo() - 1)) as [Start Accruing]
While IterNo() <= FrequencyNum;
LOAD Pick(Match(Frequency, 'Quarterly', '6 Monthly', 'Annually'), 3, 6, 12) as FrequencyNum,
MonthName(Date#([Start Accruing], 'MMM-YY')) as [Start Accruing],
Frequency,
Code,
Dept,
Total,
Monthly,
Description,
Periodcovered,
PP;
LOAD * Inline [
Code, Dept, Total, Monthly, Description, Periodcovered, Frequency, Start Accruing, PP
7455, 561, 4507.80, 375.65, Mainel - Telephone maintenance, Jan14-Dec14, Annually, Jan-15, 876
];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks a lot, that did the trick!