topic Re: problem with date in QlikView

problem with date

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

Hi i have a requirement,

i have date field which is consisting date like ( 26/6/2018 18:30:44) in this format. if i have this time in after 6.00PM means need to consider as next day morning 9.00AM and if these time have in sunday means need to consider as monday.

in normal working hours there is no issue if it is after working hours means need to change as next working hour.

please help on this.

Re: problem with date

olivierrobin — Tue, 26 Jun 2018 07:03:35 GMT

hello

why don't you create a 2nd date with a formula like :

let date2 = date1 + &if(time(date)>6,1,0)

Re: problem with date

tresB — Tue, 26 Jun 2018 07:03:50 GMT

Try like:

Load

Date(If(num(WeekDay(DateWH))=7, DateWH+1,DateWH), 'DD/M/YYYY hh:mm:ss') as FinalDate;

Load

Date(if(hour(Date)>=18, Floor(Date)+1/24*9, Date), 'DD/M/YYYY hh:mm:ss') as DateWH

From <>;

Re: problem with date

manoj217 — Tue, 26 Jun 2018 07:06:11 GMT

Hi Tresesco thanks your reply but I want those two conditions in a single field

Re: problem with date

manoj217 — Tue, 26 Jun 2018 07:10:33 GMT

Hi Olivier thanks for your reply it will satisfies only one condition if time is >6 it will add some time it will change next day but I want if it is week end (sat) means upto 1PM need to consider sat only if it is after 1PM means need to consider Monday,

similarly sunday is also holiday so consider as Monday

Re: problem with date

jyothish8807 — Tue, 26 Jun 2018 07:10:38 GMT

May be this manoj,

Load Date,

if(subfield(Subfield(Date,' ',2),':',1)>=18,date#(date(Date+1,'DD/MM/YYYY') &' 09:00:00','DD/MM/YYYY hh:mm:ss'),Date) as Date2;

LOad DAte#(Date,'DD/MM/YYYY hh:mm:ss') as Date;

LOAD * INLINE [

Date

26/6/2018 18:30:44

];

Re: problem with date

tresB — Tue, 26 Jun 2018 07:12:43 GMT

If you run the above script you would get single field as FinalDate. It's only that the two conditions are handled in two steps using preceding load. Try and let know.

Re: problem with date

jyothish8807 — Tue, 26 Jun 2018 07:24:36 GMT

Try this:

Load Date,

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

if( match(weekday(Date),'Sat') and subfield(Subfield(Date,' ',2),':',1)>=13,date#(date(Date+2,'DD/MM/YYYY') &' 09:00:00','DD/MM/YYYY hh:mm:ss'),

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

Date))) as Date2;

LOad DAte#(Date,'DD/MM/YYYY hh:mm:ss') as Date;

LOAD * INLINE [

Date

26/6/2018 18:30:44

30/6/2018 18:30:44

24/6/2018 18:30:44

];

Re: problem with date

vinod22kv — Fri, 29 Jun 2018 10:40:08 GMT

Hi Jyothish,

Can you please reply to below link

Help need In dates

Thanks and Regards,

Vinod.