Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Its not working
Make sure you use the right masks in the Timestamp#() function.
Else post your actual script here, so we can troubleshoot.
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>
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
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
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.
its working....thanks a lot