topic Re: Linking Budget data to Fact table in QlikView

Linking Budget data to Fact table

Tue, 12 May 2015 08:49:19 GMT

I have a two years of data in my data model that I have already built. I now need to bring in two tables containing budget information for the two years. The two budget tables has no date fields. The table structure is as below:

Note: M1-M2 represents Jan - Dec respectively.

Budget 2010:

Item Code, Item Desc, M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12

A1, Tiles, 12, 143, 56, 780, 623, 71, 64, 78, 54, 54, 81,10

A2, Geyser, 15, 154, 543, 721, 643, 64, 87, 09, 987, 09, 76,43

A3, Taps, 19, 15, 182, 765, 661, 71, 63, 87, 909, 81, 91,61

A4,Mixers, 81, 151, 55, 81, 451, 61, 71, 61, 41, 51, 85,98

A5, Tubs, 15, 154, 543, 721, 643, 64, 87, 09, 987, 09, 76,43

Then I have Budget 2011 table with the same structure.

Note: In my fact table I have Item Code and Item Desc fields.

How can I bring this data into my model and be able to identify on the front end which budget amount is for which year and month?

Re: Linking Budget data to Fact table

qlikviewwizard — Tue, 12 May 2015 09:06:13 GMT

Hi tstewart,

What is the table structure of Fact Table?

Re: Linking Budget data to Fact table

jagan — Tue, 12 May 2015 09:07:20 GMT

Hi,

Try like this

Fact:

LOAD

FROM DataSource;

Budget:

CrossTable(Month, Budget, 1)

LOAD

Item Code, M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12

FROM BudgetDataSource;

Hope this helps you.

Regards,

Jagan.

Re: Linking Budget data to Fact table

linusblomberg — Tue, 12 May 2015 09:08:55 GMT

1, First you need to transpose your table (crosstable) and make M1-12 to 'Month'

2, remove the M from month

3, hardcode '2010' as year if you are manually load each table

4, 'Budget' as source

4 in your fact table you have date i suppose. you also need to add month and year to your fact table and remove them from your date calender.

5, 'Sales' as source

6 concatenate Sales and budget in to one fact table

Re: Linking Budget data to Fact table

MK_QSL — Tue, 12 May 2015 09:09:16 GMT

Create a Month Field in your Fact Table named MonthName

Month(DateField) as MonthName

Use below code from BudgetTable

TempBudget:

CrossTable(TempMonth,BudgetValue,2)

Load * Inline

[

Item Code, Item Desc, M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12

A1, Tiles, 12, 143, 56, 780, 623, 71, 64, 78, 54, 54, 81,10

A2, Geyser, 15, 154, 543, 721, 643, 64, 87, 09, 987, 09, 76,43

A3, Taps, 19, 15, 182, 765, 661, 71, 63, 87, 909, 81, 91,61

A4,Mixers, 81, 151, 55, 81, 451, 61, 71, 61, 41, 51, 85,98

A5, Tubs, 15, 154, 543, 721, 643, 64, 87, 09, 987, 09, 76,43

];

Budget:

Load

SubField('$(MonthNames)',';',MonthNum) as MonthName;

Load

Num(KeepChar(TempMonth,'0123456789')) as MonthNum

Resident TempBudget;

Drop Table TempBudget;

Drop Field TempMonth;

Re: Linking Budget data to Fact table

Tue, 12 May 2015 09:25:06 GMT

Thanks guys, Cross Table works perfectly