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

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

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

1 Solution

Accepted Solutions
ahmar811
Creator III
Creator III

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

View solution in original post

5 Replies
tresesco
MVP
MVP

Try like:

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

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

Anil_Babu_Samineni

Can you show error?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
trishita
Creator III
Creator III
Author

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

tresesco
MVP
MVP

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],

ahmar811
Creator III
Creator III

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