Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
mhmmd_srf
Creator II
Creator II

Condition and Logic on Date

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

2 Replies
agomes1971
Specialist II
Specialist II

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
Partner - Creator
Partner - Creator

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;



Community requirement.png