Timestamp

[Anonymous]

Hello All,

I have a Timestamp field in my data and I would want to create a new field say X that should have values Morning, Afternoon, Evening. How can I segregate my timestamp into these values?

ex :

Timestamp	X
31-01-2017 12:13:14 PM	Afternoon
31-01-2017 6:05:12 AM	Morning
31-01-2017 7:06:12 PM	Evening

Thanks

[kaushiknsolanki · Accepted solution]

Hi,

You need to use If statement to create the New Field. Like below.

Load Timestamp1,if(TimeInNumber <= num(MakeTime(11,59)),'Morning',if(TimeInNumber > num(MakeTime(11,59)) and TimeInNumber <= num(MakeTime(15,59)),'Afternoon','Evening')) as NewField;

Load timestamp(Timestamp#(Timestamp1,'DD-MM-YYYY hh:mm:ss TT')) - Floor(timestamp(Timestamp#(Timestamp1,'DD-MM-YYYY hh:mm:ss TT'))) as TimeInNumber,* inline [

Timestamp1

31-01-2017 12:13:14 PM

31-01-2017 6:05:12 AM

31-01-2017 7:06:12 PM

];

Regards,

Kaushik Solanki

[MK_QSL]

You can use something like below..

Change StartTime and EndTime according to your need for Morning, Afternoon, Evening and Night flags.

Flag:

Load

  Time(Time#(StartTime,'hh:mm:ss')) as StartTime,

  Time(Time#(EndTime,'hh:mm:ss')) as EndTime,

  Flag

Inline

[

  StartTime, EndTime, Flag

  00:00:00, 11:59:59, Morning

  12:00:00, 16:59:59, Afternoon

  17:00:00, 19:59:59, Evening

  20:00:00, 23:59:59, Night

];

Time:

Load

  *,

  Time(Frac(TimeStampField)) as TimeStampFieldTime

;

Load

  TimeStamp(TimeStamp#(TimeStampField,'DD-MM-YYYY h:mm:ss TT')) as TimeStampField

Inline

[

  TimeStampField

  31-01-2017 12:13:14 PM

  06-05-2017 6:05:12 AM

  31-01-2017 7:06:12 PM

];

Inner Join

IntervalMatch(TimeStampFieldTime)

Load StartTime, EndTime Resident Flag;

Left Join (Time)

Load * Resident Flag;

Drop Table Flag;

Drop Fields StartTime, EndTime;

[Anonymous]

Thanks Manish