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

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

Tue, 04 Feb 2014 02:46:40 GMT

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.

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

Anonymous — Tue, 04 Feb 2014 03:52:22 GMT

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.

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

Tue, 04 Feb 2014 03:56:30 GMT

Hi Waldron,

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

Please modify my above script for better understanding.

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

Anonymous — Tue, 04 Feb 2014 04:00:17 GMT

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

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

Tue, 04 Feb 2014 04:10:59 GMT

Hi Waldron,

Thank you very much. It worked perfectly:)

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

Wed, 05 Feb 2014 00:03:23 GMT

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.

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

Wed, 05 Feb 2014 04:23:30 GMT

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