topic How to split a date field into two with given interval of time in days in QlikView

How to split a date field into two with given interval of time in days

trishita — Wed, 25 Nov 2020 16:16:04 GMT

LOAD

FUEL_TYPE,
IMO_NUMBER,
LOWER_HEATING_VALUE,
SOUNDING_TIME_TIMEZONE,
SOUNDING_TIME_UTC,

FROM
mo_purch_sounding_correction_view.qvd

(qvd);

IThe column SOUNDING_TIME_UTC is of format yy-mm-dd hh:mm.I want to split this column and make 2 column from this specific field

Before_sounding and after_sounding .The after sounding field should be 7 days apart the befpre_sounding field.

ie if Before_Sounding is 2018-05-01 then After Sounding will be 2018-05-08. Always seven days interval

Re: How to split a date field into two with given interval of time in days

Miguel_Angel_Baeyens — Thu, 03 May 2018 10:11:07 GMT

Do the same load, and add these two lines:

LOAD ... // the fields here

SOUNDING_TIME_UTC +7 AS After_Sounding,

SOUNDING_TIME_UTC -7 AS Before_Sounding

FROM file.qvd (qvd);

Make sure that the field is understood as a date according to your computer and the script variables, otherwise you will need to use the Timestamp#() function.

Re: How to split a date field into two with given interval of time in days

trishita — Thu, 03 May 2018 12:16:28 GMT

Its not working

Re: How to split a date field into two with given interval of time in days

Miguel_Angel_Baeyens — Thu, 03 May 2018 12:31:27 GMT

Make sure you use the right masks in the Timestamp#() function.

Else post your actual script here, so we can troubleshoot.

Re: How to split a date field into two with given interval of time in days

trishita — Thu, 03 May 2018 12:53:30 GMT

LOAD

IMO_NUMBER,
LOWER_HEATING_VALUE,
SOUNDING_TIME_TIMEZONE,
SOUNDING_TIME_UTC,
Date(Floor(Timestamp#(SOUNDING_TIME_UTC,'YYYY-MM-DD hh:mm')),'YYYY-MM-DD') AS SOUNDING_START_DATE,
SOUNDING_START_DATE +7 AS SOUNDING_END_DATE,
SULPHUR_CONTENT,
TEMPERATURE,
VISCOSITY,
WATER_CONTENT
FROM
[mo_purch_sounding_correction_view.qvd]

(qvd);

I CANT SEE THE DESIRED RESULT :I WANT TO DISPLAY SOUNDING_START_DATE AS "=2018-05-01 and SOUNDING_END_DATE as

2018-05-08.

SOUNDING_START_DATE is showing properly but SOUNDING_END_DATE is not.Infact it is showing error as field not find <SOUNDING_START_DATE>

Re: How to split a date field into two with given interval of time in days

sumanta12 — Thu, 03 May 2018 13:06:05 GMT

Don't use like this:

SOUNDING_START_DATE +7 AS SOUNDING_END_DATE,

Use like this:

Date(Date(Floor(Timestamp#(SOUNDING_TIME_UTC,'YYYY-MM-DD hh:mm')),'YYYY-MM-DD')+7 ) As SOUNDING_END_DATE

Re: How to split a date field into two with given interval of time in days

trishita — Thu, 03 May 2018 13:27:00 GMT

For SOUNDING_START_DATE its showing 2015-09-28

but for SOUNDING_END_DATE its showing 05.10.2015

How can I change the pattern of end date like start date. I have to perform an interval match function based on these date pattern

Re: How to split a date field into two with given interval of time in days

Miguel_Angel_Baeyens — Thu, 03 May 2018 13:34:08 GMT

You must do this within the Date() function to respect the mask you are specifying:

Date(Floor(Timestamp#(SOUNDING_TIME_UTC, 'YYYY-MM-DD hh:mm')) -7, 'YYYY-MM-DD') AS SOUNDING_START_DATE

The placing of the +7 or -7 will vary if you use different or more numeric or date functions.

Re: How to split a date field into two with given interval of time in days

trishita — Thu, 03 May 2018 14:02:01 GMT

its working....thanks a lot