too many values when putting together fact and dimensio tables

[#1]

Hi,

I have a problem when putting together, in my script, one tab containing a simple Load from a fact table:

PURCHASES:
LOAD
     COUNTRY_ID,
     DATE_ID,
     MAT_ID_MATERIAL,
     PURCHASING_ORG_ID,
     STORAGE_LOCATION_ID,
     S_SUPPLIER_ID,
     BU_PURCH_ID,
     CR_ID_CURRENCY,
     TOTAL_QUANTITY,
     VOLUME,
     LOCAL_ADDED_COST,
     LOCAL_TOTAL_PRICE,
     EUR_ADDED_COST,
     EUR_TOTAL_PRICE,
     USD_ADDED_COST,
     USD_TOTAL_PRICE
from
[ETL_F_PURCHASES_ACQ_RM.qvd](qvd);

and one tab containing a Load from a dimension table:

Material:
LOAD                 MAT_ID_MATERIAL,
                      MG_CODE_MATERIAL_GROUP,
                      MG_DESCRIPTION,
                      MG_CODE_CLASS,
                      MG_DESCRIPTION_CLASS,
                      MG_CODE_MAT_GROUP_LEV1,
                      MG_DESCRIPTION_MG_LEV1,
                      MG_CODE_MAT_GROUP_LEV1 & ' - ' & MG_DESCRIPTION_MG_LEV1 as MATERIAL_GROUP,
                      MGD_CODE_MAT_GROUP_DETAIL,
                      MGD_DESCRIPTION,
                      MTART,
                      MAT_MATERIAL_CODE,
                      MAT_DESCRIPTION,
                      MAT_MATERIAL_CODE & ' - ' & MAT_DESCRIPTION AS MATERIAL
FROM
[ETL_DIM_MATERIAL.qvd](qvd);

The problem is that in my data it results some MAT_ID_MATERIAL from the dimension table which don't have any fact.
It is like an outer join between them and not a join.
Is this a normal behavior? I was expected to visualize only the MAT_ID_MATERIAL for which I have facts.

Can you explain me this behavior and suggest me how to solve the problem?

Thanks

[Peter_Cammaert · Accepted solution]

You want load only those material definitions that have data in the facts table.Add the following clause at the end of your LOAD for table Material:

...

WHERE exists(MAT_ID_MATERIAL);

Best,

Peter

[#3]

this is normal.

If you want to remove the MAT_ID_MATERIAL which the FACT do not have you can do is:

1. always load the FACT table 1st.

2. Add WHERE EXISTS () in the following tables. for example:

Material:

LOAD            MAT_ID_MATERIAL,

                      MG_CODE_MATERIAL_GROUP,

                      MG_DESCRIPTION,

                      MG_CODE_CLASS,

                      MG_DESCRIPTION_CLASS,

                      MG_CODE_MAT_GROUP_LEV1,

                      MG_DESCRIPTION_MG_LEV1,

                      MG_CODE_MAT_GROUP_LEV1 & ' - ' & MG_DESCRIPTION_MG_LEV1 as MATERIAL_GROUP,

                      MGD_CODE_MAT_GROUP_DETAIL,

                      MGD_DESCRIPTION,

                      MTART,

                      MAT_MATERIAL_CODE,

                      MAT_DESCRIPTION,

                      MAT_MATERIAL_CODE & ' - ' & MAT_DESCRIPTION AS MATERIAL

WHERE EXISTS (MAT_ID_MATERIAL, MAT_ID_MATERIAL)

FROM

[ETL_DIM_MATERIAL.qvd](qvd);

[Peter_Cammaert]

What happens in your original application is this:

Material table contains all definitions for all products, even if they do not have facts in PURCHASES. After loading the tables, QlikView will determine associations, which are comparable - as you said - to an OUTER JOIN, e.g. a list box for field MAT_ID_MATERIAL will present all product identifications from both the field MAT_ID_MATERIAL in Material AND field MAT_ID_MATERIAL in PURCHASES.

Peter

[Peter_Cammaert]

Put the FROM before the WHERE clause. You cannot reverse the order.

Peter