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: 
vinod22kv
Creator
Creator

Help need In dates

Hi All,

I have the E Mail received date and time field as given bellow. and my working hrs. is b/w 9 am to 6 pm. now I have to create the new date and time field with this conduction if the mail receives in non-working hrs. then the date and time has to change to next day working hrs. That is next day 9 am.

and in Saturday working hrs. is up to 3 pm and  mails received after 3 pm and Sunday has to be added to Monday morning 9 am.

Sample data is attached below

Can you please please help me with the Sample QVW.

Thanks and Regards,

Vinod.

5/26/2018 9:35
5/26/2018 9:36
5/26/2018 9:40
5/26/2018 9:40
5/26/2018 9:40
5/26/2018 9:40
5/26/2018 9:40
5/26/2018 9:40
5/26/2018 9:41
5/26/2018 9:42
5/26/2018 9:43
5/26/2018 9:43
5/26/2018 9:44
5/26/2018 9:44
5/26/2018 9:44
1 Solution

Accepted Solutions
sasikanth
Master
Master

Communit.PNG

Its working fine please find the application attached, attached app for reference

View solution in original post

8 Replies
YoussefBelloum
Champion
Champion

Hi,

what is the working hours for you ? it starts and end at what time exactly ?

vinod22kv
Creator
Creator
Author

9 AM TO 6 PM

YoussefBelloum
Champion
Champion

try this:

=if( Date(TimeStamp#(FIELD,'M/DD/YYYY hh:mm'),'WWWW')='Saturday' and Timestamp(TimeStamp#(FIELD,'M/DD/YYYY hh:mm'),'hh') > 3, TimeStamp#(FIELD,'M/DD/YYYY hh:mm')+2,

if(Timestamp(TimeStamp#(FIELD,'M/DD/YYYY hh:mm'),'hh') > 6 or Timestamp(TimeStamp#(FIELD,'M/DD/YYYY hh:mm'),'hh') <9, TimeStamp#(FIELD,'M/DD/YYYY hh:mm')+1) as FIELD

vinod22kv
Creator
Creator
Author

Hi,

Thank for your reply.

But if i receive the mail at 8 pm as per your code it will add one day to it. So it will become next day 8 pm still it will not comes under working hrs....

Thanks and Regards,

Vinod

sasikanth
Master
Master

HI

Try below code in your script

LOAD [Date and Time],

  IF(  ((WeekDay([Date and Time])='Sun') or (WeekDay([Date and Time])='Sat' and Hour(([Date and Time]))>15)),

        timestamp(Timestamp( weekstart([Date and Time]+1)) +(540/24/60)),

    IF(  (WeekDay([Date and Time])<>'Sun' and WeekDay([Date and Time])<>'Sat' and ( Hour(([Date and        Time]))>15)),

      timestamp(DayStart([Date and Time],1)+(540/24/60)),[Date and Time] )) as NewDate

       

FROM

(ooxml, embedded labels, table is Sheet1);

Thanks

vinod22kv
Creator
Creator
Author

Hi,

I have checked your script,

I have selected 05/20/2018 7:00:05 pm and new date is showing 05/20/2018 9:00:00:am this is wrong date has to be 21 inested of 20.

Thanks and Regards,

Vinod.

manoj217
Creator III
Creator III

Load Date,

if(subfield(Subfield(Date,' ',2),':',1)>=18 and not match(weekday(Date),'Sat','Sun'),date#(date(Date+1,'M/DD/YYYY') &' 09:00:00','M/DD/YYYY HH:MM'),

if( match(weekday(Date),'Sat') and subfield(Subfield(Date,' ',2),':',1)>=15,date#(date(Date+2,'M/DD/YYYY') &' 09:00:00','M/DD/YYYY HH:MM'),

if( match(weekday(Date),'Sun'),date#(date(Date+1,'M/DD/YYYY') &' 09:00:00','M/DD/YYYY HH:MM'),

Date)))  as Date2

sasikanth
Master
Master

Communit.PNG

Its working fine please find the application attached, attached app for reference