Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
trishita
Creator III
Creator III

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

 

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

 

8 Replies
Miguel_Angel_Baeyens

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.

trishita
Creator III
Creator III
Author

Its not working

Miguel_Angel_Baeyens

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

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

trishita
Creator III
Creator III
Author

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>

sumanta12
Creator II
Creator II

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

trishita
Creator III
Creator III
Author

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

Miguel_Angel_Baeyens

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.

trishita
Creator III
Creator III
Author

its working....thanks a lot