How to merge records from two different fields from same table

[#1]

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

[PrashantSangle]

Hi,

not possible in single if statement.

If you want both in same field then try like

Flag:

LOAD EmpID,Date,

   if(frac(min(Time))<=$(vEarlyTime),'Early','Late') as Flag

Resident EmployeeTimeData

Group By EmpID,Date;

concatenate

LOAD EmpID,Date,

   if(frac(max(Time))>=$(vOverTime),'Over','Within') as Flag

Resident EmployeeTimeData

Group By EmpID,Date;

Regards

[Anonymous]

Dear, yes i know and i want to know the values for checking purpose and also helpfull for write script, kindly put the flag values manually as i mention above.

Regards,

Zain.

[sasiparupudi1]

Hi

What are the values in the variables

$(vEarlyTime) and $(vOverTime)

[#5]

set vEarlyTime=(7*60+10)/(24*60);

set vOverTime=16/24;

[PrashantSangle]

hi,

Did you try my suggetsion??

Regards

[#7]

By using this i got the required answer but i am getting association between time and (early,Late)not getting association between time and(over,within)

[Anonymous]

id	EmpID	EventID	Date	Time	minTime	vEarlyTime	vOverTime	maxTime
1	30015746	0	08/02/2015	7:50:00 AM	7:50:00 AM	7:10:00 AM	4:00:00 PM	4:30:00 PM
2	30015746	1	08/02/2015	8:20:00 AM	7:50:00 AM	7:10:00 AM	4:00:00 PM	4:30:00 PM
3	30015746	0	08/02/2015	8:40:00 AM	7:50:00 AM	7:10:00 AM	4:00:00 PM	4:30:00 PM
4	30015746	1	08/02/2015	10:30:00 AM	7:50:00 AM	7:10:00 AM	4:00:00 PM	4:30:00 PM
5	30015746	0	08/02/2015	10:50:00 AM	7:50:00 AM	7:10:00 AM	4:00:00 PM	4:30:00 PM
6	30015746	1	08/02/2015	11:30:00 AM	7:50:00 AM	7:10:00 AM	4:00:00 PM	4:30:00 PM
7	30015746	0	08/02/2015	12:40:00 PM	7:50:00 AM	7:10:00 AM	4:00:00 PM	4:30:00 PM
8	30015746	1	08/02/2015	2:50:00 PM	7:50:00 AM	7:10:00 AM	4:00:00 PM	4:30:00 PM
9	30015746	0	08/02/2015	3:10:00 PM	7:50:00 AM	7:10:00 AM	4:00:00 PM	4:30:00 PM
10	30015746	1	08/02/2015	4:30:00 PM	7:50:00 AM	7:10:00 AM	4:00:00 PM	4:30:00 PM

Kindly fill flag value and revert.

Regards,

Zain.

[sasiparupudi1 · Accepted solution]

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

[#10]

Hi,

Please find the attached QVW. !

[#11]

i dont have licensed version plz share script