topic Re: Help need In dates in QlikView

Help need In dates

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

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:41

5/26/2018 9:42

5/26/2018 9:43

5/26/2018 9:44

Re: Help need In dates

YoussefBelloum — Fri, 29 Jun 2018 09:25:43 GMT

Hi,

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

Re: Help need In dates

vinod22kv — Fri, 29 Jun 2018 09:31:39 GMT

9 AM TO 6 PM

Re: Help need In dates

YoussefBelloum — Fri, 29 Jun 2018 09:40:29 GMT

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

Re: Help need In dates

vinod22kv — Fri, 29 Jun 2018 09:50:03 GMT

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

Re: Help need In dates

sasikanth — Fri, 29 Jun 2018 10:38:50 GMT

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

Re: Help need In dates

vinod22kv — Fri, 29 Jun 2018 10:55:10 GMT

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.

Re: Help need In dates

manoj217 — Fri, 29 Jun 2018 10:57:15 GMT

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

Re: Help need In dates

sasikanth — Fri, 29 Jun 2018 11:01:33 GMT

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