Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Thanks Stefan!