Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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);
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;
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
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.
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);
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