topic I need to write script for a date interval for survey date in QlikView

I need to write script for a date interval for survey date

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

I need to add two dates for a specific survey date range, 2 days before the survey and seven days after it.

LOAD SURVEY_ID,

SURVEY_ID as SURVEY_ID_General_Information,

num(SURVEY_DATE) as SURVEY_DATE_NUM,

SURVEY_DATE,

SURVEY_DATE -2 as SURVEY START DATE,

SURVEY_DATE +7 as SURVEY END DATE,

IMO_NO,

Upper (SHIP_NAME) as SHIP_NAME,

OWNER,

KIND as SURVEY_TYPE,

VOYAGE_NO

FROM

[\\ww.hl.lan\HH1\Groups\RHH0T432 - ROB project\14 - QlikView\COMPASS BIAC\RoB - QDF structure 01\QlikViewStorage\SourceDocuments\10.Operations\3.Remaining On Board\2.QVD\mo_bun_sur_survey_view.qvd]

(qvd);

But I am currently having error.Can someone help me with my syntax

Re: I need to write script for a date interval for survey date

tresB — Wed, 04 Jul 2018 11:13:30 GMT

Try like:

Date(SURVEY_DATE -2) as [SURVEY START DATE],

Date(SURVEY_DATE +7) as [SURVEY END DATE],

Re: I need to write script for a date interval for survey date

Anil_Babu_Samineni — Wed, 04 Jul 2018 11:18:49 GMT

Can you show error?

Re: I need to write script for a date interval for survey date

trishita — Wed, 04 Jul 2018 11:52:53 GMT

i tried it this way but i want to have the date format in the way 'YYYY-MM-DD' BUT its showing dd.mm.yyyy format.

i want to have it in YYYY-MM-DD' format

Re: I need to write script for a date interval for survey date

tresB — Wed, 04 Jul 2018 11:57:04 GMT

I am assuming that you wanted to reply and mark on my reply. You can use the desired format in date(), like:

Date(SURVEY_DATE -2, 'YYYY-MM-DD') as [SURVEY START DATE],

Date(SURVEY_DATE +7, 'YYYY-MM-DD') as [SURVEY END DATE],

Re: I need to write script for a date interval for survey date

ahmar811 — Wed, 04 Jul 2018 11:58:46 GMT

first, you have to check if your date is in number format/Date or String.

if it is not in number format you first have to convert into the number using Date#() function and use tresesco b‌ expression like below :

suppose your date format in the string like this "22 Apr 2018"

use expression:

Date(Date#(SURVEY_DATE,'DD MMM YYYY')-2) as [SURVEY START DATE],

Date(Date#(SURVEY_DATE,'DD MMM YYYY')+7) as [SURVEY END DATE],

if you have date in Number/Date format use below

Date(SURVEY_DATE-2,'DD MMM YYYY') as [SURVEY START DATE],

Date(SURVEY_DATE+7,'DD MMM YYYY') as [SURVEY END DATE],

I hope this will help youj

Regards

Ahmar