Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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)

1 Solution

Accepted Solutions
tracysmart
Creator II
Creator II

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

View solution in original post

5 Replies
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
Creator II
Creator II

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

Not applicable
Author

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
Creator II
Creator II

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

Not applicable
Author

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