topic Re: Show all (dim_values), no fact values in QlikView

Show all (dim_values), no fact values

Anonymous — Wed, 17 Feb 2016 10:22:47 GMT

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

Re: Show all (dim_values), no fact values

Gysbert_Wassenaar — Wed, 17 Feb 2016 17:42:26 GMT

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

Re: Show all (dim_values), no fact values

sunny_talwar — Wed, 17 Feb 2016 18:05:27 GMT

or Only() function will also work:

Only({<Warehouse>} Inventory)

Re: Show all (dim_values), no fact values

Saravanan_Desingh — Wed, 17 Feb 2016 18:21:43 GMT

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

Re: Show all (dim_values), no fact values

Anonymous — Wed, 17 Feb 2016 20:33:52 GMT

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

Re: Show all (dim_values), no fact values

sunny_talwar — Wed, 17 Feb 2016 21:31:14 GMT

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

Re: Show all (dim_values), no fact values

Anonymous — Thu, 18 Feb 2016 03:26:43 GMT

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

Re: Show all (dim_values), no fact values

sunny_talwar — Thu, 18 Feb 2016 11:18:18 GMT

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

Re: Show all (dim_values), no fact values

Anonymous — Sat, 20 Feb 2016 00:28:35 GMT

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

Many thanks

Re: Show all (dim_values), no fact values

sunny_talwar — Sat, 20 Feb 2016 12:52:09 GMT

Awesome, I am glad we were able to help you