Linking Budget data to Fact table

[#1]

Hi

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?

[qlikviewwizard]

Hi tstewart,

What is the table structure of Fact Table?

[jagan · Accepted solution]

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.

[linusblomberg]

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

[MK_QSL]

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;

[#6]

Thanks guys, Cross Table works perfectly