topic Automatically incrementing date during load. in QlikView

Automatically incrementing date during load.

Gethyn — Thu, 28 Oct 2010 09:08:26 GMT

Hi,

I have a table loading from a SQL server and I need to load the data each day but restrict the data so that it only loads up to the end of the current day defined by one of the date fields in the table.

At the moment I have to manually change the date every day before the load, meaning that I can't use publisher to reload the data. Does anyone know a way that I can pick up the the end of current day or start of next day so that I don't have to hard code a date?



DutyAssignment:
LOAD "DUTY_ID",
 "OU_ID",
 applymap('CostCentreMap',"OU_ID") as [Cost Centre],
 date([PLANNED_DATE_TIME_START]) as [Shift Start],
 "ID_NUMBER" as Employee_ID,
 "SHT_ID",
 "DUTY_CANCEL_REASON_ID",
 "DUTY_CHANGE_REASON_ID",
 "PLANNED_DATE_TIME_START",
 Year(PLANNED_DATE_TIME_START) as Year,
 Month(PLANNED_DATE_TIME_START) as Month,
 Day(PLANNED_DATE_TIME_START) as Day,
 "PLANNED_DATE_TIME_END",
 "ESTIMATED_MINUTES",
 "TEMP_STAFF_REQ_REASON_ID",
 "RST_LOCATION_ID";
SQL SELECT *
FROM MAPSMSTR1.dbo."RST_DUTY_ASSIGNMENT"
Where VALID_FROM_DATE <= '2010-10-28 00:00:00.000';

Thanks,

Gethyn.

Automatically incrementing date during load.

sparur — Thu, 28 Oct 2010 11:01:43 GMT

hello

I think you should use today() function with variable:

LAET vToday = today();

SQL SELECT * FROM ....

WHERE VALID_FROM_DATE <= '$(vToday)';

Automatically incrementing date during load.

Gethyn — Thu, 28 Oct 2010 12:13:52 GMT

Hi Anatoly,

That worked well, I just had to modify it slightly to format the date the same as the field.

LET vToday = date(today(), 'YYYY-MM-DD');

Thanks,

Gethyn.

Automatically incrementing date during load.

sparur — Thu, 28 Oct 2010 12:24:12 GMT

I glad to help