Condition and Logic on Date

[mhmmd_srf]

Hi All,

I have a Field called Start Time in MM/DD/YYYY hh:mm:ss format.

I need a logic like below::

if the time falls under 10:00:00 and 22:00:00 the we need to add 2 hour to Start Time,

if time < 10:00:00 then we need to show 12:00:00 on the same day

if time> 22:00:00, then we need to show next day 12:00:00

Please help to make this in qlik script.

Thanks,

Sarif

[agomes1971]

Hi,

maybe this can help:

= if(hour(now()) >= 10 and hour(today()) <=22, date(Today())&' '&Text(Time(round( (time(now())+(2/24)), (2/24)), 'hh:mm:ss')), if(hour(now()) < 10, date(today())&' '&'12:00:00',

if(hour(now())>22,date(Today()+1)&' '&'12:00:00')))

Thanks

André Gomes

[joseph_eftamand]

Full script I used, I think this covers your requirement:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET CollationLocale='en-GB';

TABLE1:

LOAD * INLINE [

    id, Time

    1, 02/05/2018 21:00:00

    2, 02/05/2018 22:00:00

    3, 02/05/2018 23:00:00

    4, 02/05/2018 19:00:00

    5, 02/05/2018 17:00:00

    6, 02/05/2018 12:00:00

    7, 02/05/2018 11:00:00

    8, 02/05/2018 10:00:00

    9, 02/05/2018 9:00:00

    10, 02/05/2018 15:00:00

    11, 02/05/2018 16:00:00

    12, 02/05/2018 8:00:00

    13, 02/05/2018 7:00:00

    14, 02/05/2018 00:00:00

];

NoConcatenate

Table:

LOAD

id AS ID,

Timestamp#(Time,'MM/DD/YYYY hh:mm:ss') as TimeStamp

Resident TABLE1;

Logic:

LOAD

ID,

TimeStamp#(if(hour(TimeStamp) <= 10, Date(TimeStamp,'MM/DD/YYYY')& ' ' &MakeTime(Hour(TimeStamp)+2,Minute(TimeStamp),Second(TimeStamp)),

if(hour(TimeStamp) >= 22 AND hour(TimeStamp) <> 0, Date(Floor(Date(TimeStamp))+1,'MM/DD/YYYY')& ' ' &MakeTime(if(Hour(TimeStamp)=22,00,

if(Hour(TimeStamp) = 23, 01 )),Minute(TimeStamp),Second(TimeStamp)),

     Date(TimeStamp,'MM/DD/YYYY')& ' ' &MakeTime(Hour(TimeStamp),Minute(TimeStamp),Second(TimeStamp))

   )),'MM/DD/YYYY hh:mm:ss') as New_Time

Resident Table;

DROP Table Table;