Show all (dim_values), no fact values

[Anonymous]

Hey qlikers

have a problem to display the whole item assortment where the fact table doesnt include the stock information for the items.

im using a dim_item table which includes

item

Label

Materialtype

and a fact table

item

warehouse

stock quantity

the fact_table includes just item with stock, if an item doesnt have stock, no data will be stored in the database.

by suppressing the zero values, i will get the whole assortment displayed, that not a problem

If an user will select the warehouse then some items will disapear as there is no item/warehosue data in the fact table.

the result which im looking for is ,that all items will be displayed, by keeping up the filter function from the dim_table.

Can somebody help on this?

Many thanks

[Gysbert_Wassenaar]

Change your expression to sum({<Warehouse=>}Inventory)

[sunny_talwar]

or Only() function will also work:

Only({<Warehouse>} Inventory)

[Saravanan_Desingh]

Nice tip,Sunny. I was in the impression that '=' is mandatory after the dimension field.

[Anonymous]

Thanks for feedback first

The target is that the user still will be able to use the Warehouse as filter criteria ...

Im thinking to load the dim_item (assortment) to the fact_table by enter the value 0 in the inventory field, this for sure will add a lot of data to the fact_table. Im not sure whether this is the correct way.

Dim_Warehouse:

LOAD Warehouse,

    WH_Type,

    WH_Location

FROM

(ooxml, embedded labels, table is Dim_Warehouse);

FactTable:

LOAD ItemNo,

    Inventory,

    Warehouse

FROM

(ooxml, embedded labels, table is Fact);

Concatenate (FactTable)

LOAD

  ItemNo

FROM

(ooxml, embedded labels, table is Dim_Item)

Where not Exists (ItemNo);

Dim_Item:

LOAD ItemNo,

    Label,

    MaterialType,

    MainWarehouse

FROM

(ooxml, embedded labels, table is Dim_Item);

By doing this, i have  integrated the whole assortment to the fact_table. Next would be to add the MainWarehouse (unique Warehouse by Item) from the dim_Item table as Warehouse to the fact_table by mapping load and applymap. It would be just to much to add every warehouse (200 location) item (100000) relation to the fact_table.  20000000 lines and the most with value 0

If this could be the correct way, it would be great if you can show me how to perform the mapping, for items in the fact_table where the warehouse dosnt exist.

Thanks

[sunny_talwar]

What if you do this instead of mapping?

Dim_Warehouse:

LOAD Warehouse,

    WH_Type,

    WH_Location

FROM

[DataTestItem (2).xlsx]

(ooxml, embedded labels, table is Dim_Warehouse);

FactTable:

LOAD ItemNo,

    Inventory,

    Warehouse

FROM

[DataTestItem (2).xlsx]

(ooxml, embedded labels, table is Fact);

Concatenate (FactTable)

LOAD

  ItemNo,

  MainWarehouse as Warehouse

FROM

[DataTestItem (2).xlsx]

(ooxml, embedded labels, table is Dim_Item)

Where not Exists (ItemNo);

Dim_Item:

LOAD ItemNo,

    Label,

    MaterialType,

    MainWarehouse

FROM

[DataTestItem (2).xlsx]

(ooxml, embedded labels, table is Dim_Item);

[Anonymous]

It would be smart Just forgot to add the item Warehouse relation in the previous post.

So by right something like this could work, but if i rename the fields i get to the next problem. Any quick fix on this?

FactTable:

LOAD ItemNo,

    Inventory,

    Warehouse,

  ItemNo&'|'&Warehouse as ItemWarehouse

FROM

[DataTestItem (2).xlsx]

(ooxml, embedded labels, table is Fact);

Concatenate (FactTable)

LOAD

  ItemNo,

  MainWarehouse as Warehouse

  ItemNo&'|'&MainWarehouse as ItemWarehouse

FROM

[DataTestItem (2).xlsx]

(ooxml, embedded labels, table is Dim_Item)

Where not Exists (ItemWarehouse);

Thanks

[sunny_talwar · Accepted solution]

May be this:

FactTable:

LOAD ItemNo,

    Inventory,

    Warehouse,

  ItemNo&'|'&Warehouse as ItemWarehouse

FROM

[DataTestItem (2).xlsx]

(ooxml, embedded labels, table is Fact);

Concatenate (FactTable)

LOAD

  ItemNo,

  MainWarehouse as Warehouse

  ItemNo&'|'&MainWarehouse as ItemWarehouse

FROM

[DataTestItem (2).xlsx]

(ooxml, embedded labels, table is Dim_Item)

Where not Exists (ItemWarehouse, ItemNo&'|'&MainWarehouse);

[Anonymous]

Perfect Sunny, it works also in the full model, fast and no performance issues

Many thanks

[sunny_talwar]

Awesome, I am glad we were able to help you