Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Flag:
LOAD EmpID,Date,
if(frac(min(Time))<=$(vEarlyTime),'Early','Late') as FlagEarly,
if(frac(max(Time))>=$(vOverTime),'Over','Within') as FlagOver
Resident EmployeeTimeData
Group By EmpID,Date;
by using this i am getting
FlagEarly/Late FlagOver/Within
Early Over
Late Within
But i want to have
Flag
Early
Late
Over
Within
i am trying with nested if something like
Flag1:
LOAD EmpID,Date,
if(Frac(Min(Time))<=$(vEarlyTime),'Early',if(Frac(Min(Time))>$(vEarlyTime),'Late',if(Frac(max(Time))>=$(vOverTime),'Over',if(Frac(max(Time))<$(vOverTime),'Within')))) as Flag
Resident EmployeeTimeData
Group By EmpID,Date;
but it is giving
Flag
Early
Late
so please help me how to achieve this
set vEarlyTime=(7*60+10)/(24*60);
set vOverTime=16/24;
Data:
LOAD id,
EmpID,
EventID,
Date,
//Date(Date#(subDate,'MM-DD-YYYY'),'MM-DD-YYYY') as Date,
Time
FROM
(ooxml, embedded labels, table is Sheet1);
left join(Data)
//load EmpID,Date, Time(min(Time),'hh:mm:ss') as MinTime,Time(Max(Time),'hh:mm:ss') as MaxTime Resident Data Group By EmpID,Date;
load EmpID,Date, min(Time) as MinTime, Max(Time) as MaxTime Resident Data Group By EmpID,Date;
NoConcatenate
final:
load *, if(MinTime<=$(vEarlyTime),'Early','Late') as Flag Resident Data;
load *, if(MaxTime>=$(vOverTime),'Over','Within') as Flag Resident Data;
drop Table Data;
hth
Sasi
Hi Hemeswara Reddy
Sample data with sample app will definitely help us to provide quick answer
HTH
Sreeni
i attached the data please send your reply
Like this:
If(Frac(Min(Time))<=$(vEarlyTime), 'Early', 'Late') & '/' & If(Frac(max(Time))>=$(vOverTime), 'Over', 'Within') as Flag
you should define an time range for both calculation, otherwise you will always get 'Early' or 'Late'.
By using this i am getting
Flag
Early/over
Early/within
Late/over
but i want
Flag
Early
Late
Over
Within
You cannot. Every record can be either early or late. So no records would be ever be assigned to over/within.
id | EmpID | EventID | Date | Time | minTime | vEarlyTime | vOverTime | maxTime | fill Flag manually |
1 | 30015746 | 0 | 08/02/2015 | 7:50:00 AM | 7:50:00 AM | 8:00:00 AM | 12:00:00 PM | 4:30:00 PM | |
2 | 30015746 | 1 | 08/02/2015 | 8:20:00 AM | 7:50:00 AM | 8:00:00 AM | 12:00:00 PM | 4:30:00 PM | |
3 | 30015746 | 0 | 08/02/2015 | 8:40:00 AM | 7:50:00 AM | 8:00:00 AM | 12:00:00 PM | 4:30:00 PM | |
4 | 30015746 | 1 | 08/02/2015 | 10:30:00 AM | 7:50:00 AM | 8:00:00 AM | 12:00:00 PM | 4:30:00 PM | |
5 | 30015746 | 0 | 08/02/2015 | 10:50:00 AM | 7:50:00 AM | 8:00:00 AM | 12:00:00 PM | 4:30:00 PM | |
6 | 30015746 | 1 | 08/02/2015 | 11:30:00 AM | 7:50:00 AM | 8:00:00 AM | 12:00:00 PM | 4:30:00 PM | |
7 | 30015746 | 0 | 08/02/2015 | 12:40:00 PM | 7:50:00 AM | 8:00:00 AM | 12:00:00 PM | 4:30:00 PM | |
8 | 30015746 | 1 | 08/02/2015 | 2:50:00 PM | 7:50:00 AM | 8:00:00 AM | 12:00:00 PM | 4:30:00 PM | |
9 | 30015746 | 0 | 08/02/2015 | 3:10:00 PM | 7:50:00 AM | 8:00:00 AM | 12:00:00 PM | 4:30:00 PM | |
10 | 30015746 | 1 | 08/02/2015 | 4:30:00 PM | 7:50:00 AM | 8:00:00 AM | 12:00:00 PM | 4:30:00 PM |
kindly fill Flag column with correct value manually and reply.
Regards,
Zain.
No we have to create flag colomn in script in that column that field has to have Early,Late,Over ,within