Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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;
Thanks Manish