Flag overtime in load from Timestamp between times.

[#1]

Hello again!

I have a table with Timestamps, TimestampStart and TimestampEnd. In the load script I would like to put a flag on all those timestamps that have a TimestampStart between 19:00 in the evening and 06:00 in the morning. Can´t get it to work.

TimestampStart	TimestampEnd	OvertimeFlagg
2014-02-08 14:00	2014-02-08 14:55	0
2014-06-24 20:00	2014-06-25 20:45	1
2014-01-27 11:20	2014-01-27 13:00	0
2014-01-01 15:00	2014-01-01 15:05	0
2014-01-01 19:30	2014-01-01 19:40	1
2014-01-05 05:00	2014-01-05 05:25	1
2014-02-19 13:00	2014-02-19 13:05	0
2014-02-26 09:15	2014-02-26 09:35	1

Regards

Mårten

[yduval75 · Accepted solution]

If(Hour(TimestampStart*1)>=19 and Hour(TimestampEnd*1)<=6, 1, 0) as OvertimeFlagg

[MK_QSL]

Load

  TimestampStart,

  TimestampEnd,

  If(TimestampStart >= TempStartDate and TimestampEnd <= TempEndDate, 1) as OverTimeFlag;

Load

  Timestamp#(TimestampStart,'YYYY-MM-DD hh:mm') as TimestampStart,

  TimeStamp#(Date(Floor(Timestamp#(TimestampStart,'YYYY-MM-DD hh:mm')),'YYYY-MM-DD')&' 19:00','YYYY-MM-DD hh:mm') as TempStartDate,

  Timestamp#(TimestampEnd,'YYYY-MM-DD hh:mm') as TimestampEnd,

  TimeStamp#(Date(Floor(Timestamp#(TimestampStart,'YYYY-MM-DD hh:mm'))+1,'YYYY-MM-DD')&' 06:00','YYYY-MM-DD hh:mm') as TempEndDate

Inline

[

  TimestampStart, TimestampEnd,

  2014-02-08 14:00, 2014-02-08 14:55

  2014-06-24 20:00, 2014-06-25 20:45

  2014-01-27 11:20, 2014-01-27 13:00

  2014-01-01 15:00, 2014-01-01 15:05

  2014-01-01 19:30, 2014-01-01 19:40

  2014-01-05 05:00, 2014-01-05 05:25

  2014-02-19 13:00, 2014-02-19 13:05

  2014-02-26 09:15, 2014-02-26 09:35

];

[MarcoWedel]

Hi,

one solution might be:

LOAD TimestampStart,
    TimestampEnd,
    -(Frac(TimestampStart)>'19:00:00' or Frac(TimestampStart)<'06:00:00') as OvertimeFlagg
FROM [http://community.qlik.com/thread/145980] (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 2))));

hope this helps

regards

Marco

[#5]

Thanks all, now it´s solved.

Best regards

Mårten