topic Re: How to pass Parameters (Like SSRS) in the query of QVW files to retrieve only required data? in QlikView

How to pass Parameters (Like SSRS) in the query of QVW files to retrieve only required data?

Tue, 04 Feb 2014 03:04:16 GMT

Hi,

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)

WHERE SALARY_CREADITED_DATE>='2013-12-31' //This I don't want to hard code I want to make it dynamic so user can select a date and load data greater and equal to a date. WHERE SALARY_CREADITED_DATE>=@PRM_DATE
;

This I don't want to hard code I want to make it dynamic so user can select a date and load data greater and equal to a selected date something like this WHERE SALARY_CREADITED_DATE>=@PRM_DATE

Re: How to pass Parameters (Like SSRS) in the query of QVW files to retrieve only required data?

Tue, 04 Feb 2014 04:39:02 GMT

Use this code instead of hard coding.

Var:

LOAD

Input('Enter the Date in M/DD/YYYY','Input box') AS Date1

AutoGenerate 1;

Let Var1=Num(Peek('Date1',0,'Var'));

Drop Table Var;

LOAD Date

FROM

(ooxml, embedded labels, table is Sheet1)

Where Num(Date)<=$(Var1);

Re: How to pass Parameters (Like SSRS) in the query of QVW files to retrieve only required data?

Anonymous — Tue, 04 Feb 2014 04:53:37 GMT

Assuming you have publisher refer to the following thread.

you can use the parameter as part of your where clause

Passing Parameter to Publisher task

E.g.

LOAD EMP_KEY, DEP_KEY, MONTHLY_SALARY, INCENTIVE, SALARY_CREADITED_DATE

FROM
FACT_EMP_DETAILS_DATA.QVD (qvd)

WHERE SALARY_CREADITED_DATE>=$(p.Date);

p.Date is the parameter defined in publisher.

Re: How to pass Parameters (Like SSRS) in the query of QVW files to retrieve only required data?

Wed, 05 Feb 2014 05:47:26 GMT

Hi P1ratyush,

your ans helped me and it worked as expected but I need further advise to set default values to variable.

V_PRM_START_DT:

LOAD

INPUT('Enter the Start Date in YYYYMMDD','Input box') AS START_DATE AUTOGENERATE 1;

LET PRM_START_DT=NUM(PEEK('START_DATE',0,'V_PRM_START_DT'));

DROP TABLE V_PRM_START_DT;

V_PRM_END_DT:

LOAD

INPUT('Enter the End Date in YYYYMMDD','Input box') AS END_DATE AUTOGENERATE 1;

LET PRM_END_DT=NUM(PEEK('END_DATE',0,'V_PRM_END_DT'));

DROP TABLE V_PRM_END_DT;

FACT_EMP_DATA:

LOAD

DATE_KEY,

EMP_KEY,

[MONTHLY SALARY AMOUNT],

[INCENTIVE AMOUNT]

FROM

FACT_EMP_DATA.QVD (qvd)

WHERE DATE_KEY>=$(PRM_START_DT) AND DATE_KEY<=$(PRM_END_DT);

Question:1

WHERE DATE_KEY BETWEEN $(PRM_START_DT) AND $(PRM_END_DT) is not working but WHERE DATE_KEY>=$(PRM_START_DT) AND DATE_KEY<=$(PRM_END_DT) is working. I would like to know why?

Is BETWEEN not valid command?

Question:2

I want to assign default values (Something like this CONVERT(VARCHAR(10),Getdate()-30,112) for Start Date and CONVERT(VARCHAR(10),Getdate(),112) for End Date) to my Input parameters so that if some one is not willing to enter these dates then it will reload with default values. How can I achieve this?