Data problem - Show aggregate level data at detail level

[#1]

Hi,

The data I have is as follows:

Product, PeriodStartDate, PeriodEndDate, SalesValue

A, 26/09/2011, 03/10/2011, 800.00

I want to display data like this:

Product, PeriodDate, SalesValue

A, 26/09/2011, 100.00

A, 27/09/2011, 100.00

A, 28/09/2011, 100.00

A, 29/09/2011, 100.00

A, 30/09/2011, 100.00

A, 01/10/2011, 100.00

A, 02/10/2011, 100.00

A, 03/10/2011, 100.00

(The Sales Value is divided by the No. of days in the period i.e. 800/8 = 100).

Do I need to creat an intermediate table?

Regards

Raj

[#2]

SalesValue/(PeriodEndDate-PeriodStartDate) as NewSalesValue

This should work

[#3]

Hi marcsliving

,I dont think I explained well.

I want the aggregated information that I have to be displayed in a detail manner. Could you tell me how to generate the dates between the start and end dates

[swuehl · Accepted solution]

Maybe like this:

INPUT:

LOAD * INLINE [

Product, PeriodStartDate, PeriodEndDate, SalesValue

A, 26/09/2011, 03/10/2011, 800.00

];

Result:

LOAD Product,

Date(PeriodStartDate+iterno()-1) as Date,

(SalesValue / (PeriodEndDate-PeriodStartDate+1) ) as Value

resident INPUT while PeriodStartDate+IterNo()-1 <= PeriodEndDate;

Hope this helps,

Stefan

[#5]

Hi Stefan,

that's absolutely amazing. I was trying with the for loop. But this is much easier with the iterno() and while loop.

Thank You!