topic Re: How to add a 'Unknown' value/record in dimension table? in QlikView

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

Wed, 17 Aug 2016 12:41:09 GMT

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)

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

Kushal_Chawda — Wed, 17 Aug 2016 12:59:40 GMT

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;

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

tracysmart — Wed, 17 Aug 2016 13:06:31 GMT

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

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

Wed, 17 Aug 2016 15:52:35 GMT

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;

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

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

tracysmart — Thu, 18 Aug 2016 07:22:07 GMT

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);

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

Thu, 13 Oct 2016 09:58:37 GMT

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.