topic Merging Budget data into Actual Transactions in load script in QlikView

Merging Budget data into Actual Transactions in load script

pduplessis — Mon, 04 Oct 2010 01:00:58 GMT

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.

Merging Budget data into Actual Transactions in load script

renjithpl — Mon, 04 Oct 2010 06:49:49 GMT

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

Merging Budget data into Actual Transactions in load script

pduplessis — Mon, 04 Oct 2010 14:52:47 GMT

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

Merging Budget data into Actual Transactions in load script

renjithpl — Wed, 13 Oct 2010 06:49:16 GMT

Hi,

Could you send your tables in in excel format. ?

Regards

Merging Budget data into Actual Transactions in load script

Wed, 13 Oct 2010 07:59:47 GMT

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