Loading Budget and Target data to Sales (Fact) data

[chiso_chiso]

Gurus,

I have a spreadsheet with Target figure and another with Budget figures which i need to tie back to my Sales table:

I am struggling with a Link Table.

The Script is like below;

Targets:

LOAD YearMonth,

     Quarter,

     Target

FROM Target.xls;

Budget:

LOAD

     BUC,

     Product,

     Source,

     [BusinessChannel],

     [Campaign Type],

     [YearMonth],

     FY,

     Budget,

     Quarter as BudgetQuarter,

     [Financial Year] as F_Year,

     [Daily Budget]

FROM Budget.xls;

Sales:

LOAD

     ContractNumber,

      Converted,

     Status as [Contracted Status],

     StatusDate,

     DateOfSale,

     PolicyDate,

     Date(Floor(PolicyDate)) As SaleDate,

     Date(Floor(StatusDate)) As Status_Date,

     Date(Floor([PolicyDate]))  as [Sales_Date],

     Time(Frac([PolicyDate]))   as [Sale Created Time],

     Month([PolicyDate]) as Sales_Month,

     num(Month([PolicyDate])) as SaleMonth_Number,

     Year([PolicyDate]) as Sales_Year,

     Day([PolicyDate]) as Sales_Day,

     Year([PolicyDate])* 100 + num(Month([PolicyDate])) as SaleYearMonth,

     SalesAgent,

     BusinessUnitCode,

     BusinessSource,

     BusinessChannel,

     CampaignCode,

     CampaignType,

     Product

FROM Sales.qvd;

Kindly assist.

Harrison

[marcus_sommer]

I think this will help you further: Re: Budget Achievement.

- Marcus

[qlikviewwizard]

Hi,

Please attach sample qvw & xls data. Thank you.

[jagan]

Hi,

Try like this

Targets:

LOAD YearMonth,

     Target

FROM Target.xls;

BudgetNSales:

LOAD

     BUC,

     Product,

     Source,

     [BusinessChannel],

     [Campaign Type],

     [YearMonth],

     FY,

     Budget,

     Quarter,

     [Financial Year] as F_Year,

     [Daily Budget]

FROM Budget.xls;

Concatenate(BudgetNSales)

LOAD

     ContractNumber,

      Converted,

     Status as [Contracted Status],

     StatusDate,

     DateOfSale,

     PolicyDate,

     Date(Floor(PolicyDate)) As SaleDate,

     Date(Floor(StatusDate)) As Status_Date,

     Date(Floor([PolicyDate]))  as [Sales_Date],

     Time(Frac([PolicyDate]))   as [Sale Created Time],

     Month([PolicyDate]) as Sales_Month,

     num(Month([PolicyDate])) as SaleMonth_Number,

     Year([PolicyDate]) as Sales_Year,

     Day([PolicyDate]) as Sales_Day,

     Year([PolicyDate])* 100 + num(Month([PolicyDate])) as YearMonth,

     SalesAgent,

     BusinessUnitCode AS BUC,

     BusinessSource,

     BusinessChannel,

     CampaignCode,

     CampaignType AS [Campaign Type],

     Product       

FROM Sales.qvd;

Regards,

Jagan.