How can hide KEY columns from FACT and DIMENSION in QVW in QlikView

[#1]

Hi,

I am trying to create Fact and Dimension model in QlikView same like my existing SSAS cube.

I have DIM_EMP_TABLE (EMP_KEY,EMP_ID, EMP_NAME,JOINING_DATE) another dimension table DIM_DEP_TABLE (DEP_KEY, DEP_ID, DEP_NAME) and Fact table FACT_EMP_DETAILS (EMP_KEY, DEP_KEY, MONTHLY_SALARY, INCENTIVE, SALARY_CREADITED_DATE)

What I have done is created a QVW file to reload data into 3 different QVD files named as DIM_EMP_DATA.QVD , DIM_DEP_DATA.QVD and FACT_EMP_DETAILS_DATA.QVD

Now Created another QVW with below query to fetch data from QVD files and display in Listbox and Table & chart.

DIM_EMP_DATA:

LOAD EMP_KEY,EMP_ID, EMP_NAME,JOINING_DATE
FROM
DIM_EMP_DATA.QVD
(qvd);

DIM_DEP_DATA:

LOAD DEP_KEY, DEP_ID, DEP_NAME
FROM
DIM_DEP_DATA.QVD
(qvd);

FACT_EMP_DETAILS_DATA:

LOAD EMP_KEY, DEP_KEY, MONTHLY_SALARY, INCENTIVE, SALARY_CREADITED_DATE

FROM
FACT_EMP_DETAILS_DATA.QVD
(qvd);

Now here I am seeing based on name automatically it is both dimension joining to fact table.

Question 1: If 2 tables having same column name but I don’t want to create join on them. How to handle this? One way I know create different alias names for both the columns. Any other way to do this?

Question 2: As EMP_KEY and DEP_KEY are system generated key and should not be exposed to end use because there is no signification of these Keyes to end users. So I want to hide this for all the users. (We have similar concept in SSAS Cube). I would like to know how do I achieve this?

Please provide me all the steps and appropriate links because I am new to QlikView.

[Anonymous]

Hi in

regards to question 1 you could use the qualify statement prior to your table load. Then all fields are prefixed with the table name.

regards to question 2 you can define certain columns as system fields.

At the start of your script insert

Set HidePrefix = '_';

I use underscore but I have seen '%' used.

[#3]

Hi Waldron,

I am new to Qlikview so Could you provide me script for the same?

Please modify my above script for better understanding.

[Anonymous · Accepted solution]

/*** set hide prefix ***/

Set HidePrefix = '_';

DIM_EMP_DATA:
LOAD EMP_KEY as _EMP_KEY

     ,EMP_ID

     ,EMP_NAME

     ,JOINING_DATE
FROM
DIM_EMP_DATA.QVD
(qvd);

DIM_DEP_DATA:
LOAD DEP_KEY as _DEP_KEY

     , DEP_ID

     , DEP_NAME
FROM
DIM_DEP_DATA.QVD
(qvd);

FACT_EMP_DETAILS_DATA:

LOAD

          EMP_KEY as _EMP_KEY,

     DEP_KEY as _DEP_KEY

     , MONTHLY_SALARY

     , INCENTIVE

     , SALARY_CREADITED_DATE

FROM
FACT_EMP_DETAILS_DATA.QVD
(qvd);

[#5]

Hi Waldron,

Thank you very much. It worked perfectly:)

[#6]

Hi Waldron,

How can I do this "you could use the qualify statement prior to your table load. Then all fields are prefixed with the table name"

Please explain by modifying above query.

[#7]

Hi,

Even after using Set HidePrefix = '_';, If end users CHECK option "Show System Fields", Those hidden fields are showing. There is anyway to hide them completely or can disable option "Show System Fields"

/*** set hide prefix ***/

Set HidePrefix = '_';

DIM_EMP_DATA:
LOAD EMP_KEY as _EMP_KEY

     ,EMP_ID

     ,EMP_NAME

     ,JOINING_DATE
FROM
DIM_EMP_DATA.QVD
(qvd);