How to generate a series of Months?

[#1]

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

[#2]

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;

[sunny_talwar]

How do you know the this needs to be until Dec-2015? Always 1 year?

[#4]

@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?

[#5]

@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

[#6]

@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

[sunny_talwar · Accepted solution]

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

];

[#8]

jaimeaguilar T

Thanks a lot, that did the trick!