topic How to create a dynamic date. in QlikView

How to create a dynamic date.

Mon, 03 Feb 2014 22:05:16 GMT

Hi - I am trying to set up a query that is dynamic and the dates will constanntly roll forward. I am not having luck with the database, using sysdate. It keeps telling me that i need to put in a specific date with hours and minutes. Is there any other syntax I can use to query for data for every week? I'd like to do something like sysdate-6, so I can schedule the reload every week.

SELECT *

FROM ENG."QTIME_SOURCE"."QTIME_SOURCE_13MONTH"
where "WORK_WEEK_ENDING_DATE" BETWEEN TO_DATE ('3-feb-2014','dd-mon-yyyy')- 6
AND TO_DATE ('3-feb-2014','dd-mon-yyyy')
and hour_type in ('OT1','OT2') ;

Re: How to create a dynamic date.

iktrayanov — Mon, 03 Feb 2014 22:51:37 GMT

Create variables

let vStartDate =date( today() - 6);

let vEndDate = today();

Then use it in your load statement

SELECT *
FROM ENG."QTIME_SOURCE"."QTIME_SOURCE_13MONTH"
where "WORK_WEEK_ENDING_DATE" BETWEEN $(vStartDate) AND $(vEndDate)
and hour_type in ('OT1','OT2') ;

Re: How to create a dynamic date.

maxgro — Mon, 03 Feb 2014 23:27:59 GMT

database sql server (103 is for dd/mm/yyyy in convert function)

let vStartDate =date((today() - 6), 'DD/MM/YYYY');

let vEndDate = date(today(), 'DD/MM/YYYY');

trace $(vStartDate) $(vEndDate);

Table:

SQL SELECT *

FROM ENG."QTIME_SOURCE"."QTIME_SOURCE_13MONTH"

where "WORK_WEEK_ENDING_DATE" between convert(datetime,'$(vStartDate)',103) and convert(datetime,'$(vEndDate)',103)

and hour_type in ('OT1','OT2') ;