topic Re: Loading Budget and Target data to Sales (Fact) data in QlikView

Loading Budget and Target data to Sales (Fact) data

chiso_chiso — Mon, 07 Sep 2015 12:48:41 GMT

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

Re: Loading Budget and Target data to Sales (Fact) data

marcus_sommer — Mon, 07 Sep 2015 13:00:59 GMT

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

- Marcus

Re: Loading Budget and Target data to Sales (Fact) data

qlikviewwizard — Tue, 08 Sep 2015 00:42:17 GMT

Hi,

Please attach sample qvw & xls data. Thank you.

Re: Loading Budget and Target data to Sales (Fact) data

jagan — Tue, 08 Sep 2015 01:04:51 GMT

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.