Merging Budget data into Actual Transactions in load script

[pduplessis]

I have budget data that gives a RunRate for the month, and I have sales data. For example the budget data is:

Month, Region, RunRate

Jan, RegionA, 355.00

Jan, RegionB, 220.00

Feb, RegionA, 360.00

Feb, RegionB, 225.00

...etc

The RunRate is the sales budget per day for the month.

I would like to link it into my sales data which has a master calendar, but I am at a loss for knowing how.

Any ideas are most welcome.

[renjithpl]

Hi,

If your Sales Data has any date field, for eg. Startdate with mm-dd-yyyy format. You can load the sales data and give something like this.

Month(Startdate) as Month,

so your Month field in Sales Data and Budget Data will get linked.

If you have any queries, please revert back.

Ren

[pduplessis]

Ranjit, thanks for you help. I have linked the 2 tables on Region, so I guess I am struggling to understand how I can link on month as well. If I link on Month instead of Region, then I have the same problem.

I thought I could roll up the RunRate into a single total of budget for the month, and then use the aggr function but that also seems to not be working.

best regards

[renjithpl]

Hi,

Could you send your tables in in excel format. ?

Regards

[#5]

Hi,

You can concatenate your budget and actuals with a flag field added to the table structure.

I conceder that you have one row for each month but the same row should be present for each day to get the daily budget.

For Example:-

Fact:

Load

Month,

Region,

RunRate,

'Budget' As Flag

from Budget.qvd;

left join

Load

Date,

Month

from MasterCalendar.qvd;

drop field Month;

concatenate (Fact)

Load

Date,

Region,

RunRate,

'Budget' As Flag

from Actuals.qvd;

This code will return a single table which will have rows for actuals as well as for Budget. Now you can link this Fact table to your master calander.

I hope this will help you.

Thanks & Best Regards,

Kuldeep Tak