Generating monthly records

[#1]

Hi,

I have a table with the following data:

Salesperson     TargetDate        Target

ABC                  01/01/2012        200

DEF                  01/01/2012        300

I would like to generate the same target for each month for each salesperson after 01/01/2012.

The data should ideally look as shown below:

Salesperson     TargetDate        Target

ABC                  01/01/2012        200

ABC                  01/02/2012        200

ABC                  01/03/2012        200

ABC                  01/04/2012        200

ABC                  01/05/2012        200

ABC                  01/06/2012        200

DEF                  01/01/2012        300

DEF                  01/02/2012        300

DEF                  01/03/2012        300

DEF                  01/04/2012        300

DEF                  01/05/2012        300

DEF                  01/06/2012        300

Can you please provide me with the script to generate the extra records. Any help will be appreciated.

Kind Regards

Raj

[swuehl · Accepted solution]

You can use a while statement to create additional records, something like

LOAD

Salesperson,

Date(addMonths(TargetDate,iterno()-1)) as TargetDate,

Target

while addmonths(TargetDate,iterno()-1) < monthstart(today());

LOAD * INLINE [

Salesperson,     TargetDate,        Target

ABC,                  01/01/2012 ,       200

DEF,                  01/01/2012 ,       300

];

This will create a record for each month from the given TargetDate in the Input table up to last month.

[#3]

Thanks Stefan!