topic Re: Generating monthly records in QlikView

Generating monthly records

Tue, 10 Jul 2012 11:46:33 GMT

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 — Tue, 10 Jul 2012 12:50:28 GMT

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.

Tue, 17 Jul 2012 15:01:14 GMT

Thanks Stefan!