Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.

1 Solution

Accepted Solutions
Damian_Waldron
Employee
Employee

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

View solution in original post

6 Replies
Damian_Waldron
Employee
Employee

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.

Not applicable
Author

Hi Waldron,

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

Please modify my above script for better understanding.

Damian_Waldron
Employee
Employee

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

View solution in original post

Not applicable
Author

Hi Waldron,


Thank you very much. It worked perfectly:)

Not applicable
Author

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.

Not applicable
Author

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