How to add a 'Unknown' value/record in dimension table?

[#1]

Dear Qlikviewers,

I am designing and implementing a dimensional model in Qlikview. But I'm having some issues with empty fields in my fact table.

The situation:

I load a fact table with multiple sorts of costs, both actuals and budgets. This 'cost fact table' is a concatenation of the actuals and the budgets.

However, all the budget records have a cost center, and the actuals don't.

In my fact table I am perfectly able to populate these empty cost center fields with 'Unknown'. With something like => (if(isnull(costcenterKEY), 'Unknown', costcenterKEY))

My dimension table 'cost center' does not have a record/value 'Unknown'.

Question:

How do I get this 'Unknown' record/value in my cost center dimension table? (which comes straight from a SAP DB)

[Kushal_Chawda]

You can append the unknown value in dimension table and link it with Fact table key

CostCenterDimension:

LOAD CostCenter

FROM table;

Concatenate(CostCenterDimension)

LOAD * inline [

CostCenter

Unknown ];

CosctCenter:

LOAD *,

           CostCenter  as costcenterKEY

resident CostCenterDimension;

drop table CostCenterDimension;

Fact:

LOAD *,

         if(isnull(costcenterKEY), 'Unknown', costcenterKEY

FROM Fact;

[tracysmart]

I would try something like this

Concatenate (MyDimensionTable)
Load
'Unknown' AS costcenterKEY
AutoGenerate(1)

if the table has other columns to populate with values then just add to the Load statement

[#4]

Thanks Tracy and Kushal for your quick and helpful replies!

I used a sort of combo of both your replies.

I'm not familiar with the AutoGenerate statement Tracy. Does it mean in this case that it only adds 1 row?

And if I replace the '1' value with 10, it would add 10 rows (with the 'Unknown' value)?

I used an 'concatenate & inline' solution, but I wanted to get a nice record like;

Unknown | Unknown_CostCenter | 01-01-2016 | 31-12-9999 | Unknown ledger |

So I changed (added all columns) the inline-solution provided by Kushal a bit.

[tracysmart · Accepted solution]

Yes Koos, Autogenerate(10) would produce 10 rows. Autogenerate is telling Qlikview how many rows the table should have. It is expecting a number but that can be derived from an expression inside the brackets.

IF you want to add extra columns to your single row  then this is how I would do it.

Concatenate (Sheet1)
Load
'Unknown' AS Company,
'Unknown_CostCenter' AS Col2Name,
'01-01-2016' AS Col3Name,
'31-12-9999' AS Col4Name,
'Unknown' AS Col5Name,
'ledger' AS Col5Name
AutoGenerate(1);

[#6]

Hi Snoeks,

I know it's a very delayed response but in case you still did not get it (because it seems to be unanswered):

I think you can try NULLASVALUE and Set NullValue statements.

Aj