Skip to main content
Announcements
Get Ready. A New Qlik Learning Experience is Coming February 17! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to merge records from two different fields from same table

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

1 Solution

Accepted Solutions
sasiparupudi1
Master III
Master III

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

View solution in original post

21 Replies
SreeniJD
Specialist
Specialist

Hi Hemeswara Reddy

Sample data with sample app will definitely help us to provide quick answer

HTH

Sreeni

Not applicable
Author

i attached the data please send your reply

jonathandienst
Partner - Champion III
Partner - Champion III

Like this:   

     If(Frac(Min(Time))<=$(vEarlyTime), 'Early', 'Late') & '/' & If(Frac(max(Time))>=$(vOverTime), 'Over', 'Within') as Flag

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
kathir
Partner - Contributor
Partner - Contributor

you should define an time range for both calculation, otherwise you will always get 'Early' or 'Late'.

Not applicable
Author

By using this i am getting

Flag

Early/over

Early/within

Late/over

but i want

Flag

Early

Late

Over

Within

jonathandienst
Partner - Champion III
Partner - Champion III

You cannot. Every record can be either early or late. So no records would be ever be assigned to over/within.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

t1.png

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

idEmpIDEventIDDateTimeminTimevEarlyTimevOverTimemaxTimefill Flag manually
130015746008/02/20157:50:00 AM7:50:00 AM8:00:00 AM12:00:00 PM4:30:00 PM
230015746108/02/20158:20:00 AM7:50:00 AM8:00:00 AM12:00:00 PM4:30:00 PM
330015746008/02/20158:40:00 AM7:50:00 AM8:00:00 AM12:00:00 PM4:30:00 PM
430015746108/02/201510:30:00 AM7:50:00 AM8:00:00 AM12:00:00 PM4:30:00 PM
530015746008/02/201510:50:00 AM7:50:00 AM8:00:00 AM12:00:00 PM4:30:00 PM
630015746108/02/201511:30:00 AM7:50:00 AM8:00:00 AM12:00:00 PM4:30:00 PM
730015746008/02/201512:40:00 PM7:50:00 AM8:00:00 AM12:00:00 PM4:30:00 PM
830015746108/02/20152:50:00 PM7:50:00 AM8:00:00 AM12:00:00 PM4:30:00 PM
930015746008/02/20153:10:00 PM7:50:00 AM8:00:00 AM12:00:00 PM4:30:00 PM
1030015746108/02/20154:30:00 PM7:50:00 AM8:00:00 AM12:00:00 PM4:30:00 PM

kindly fill Flag column with correct value manually and reply.

Regards,

Zain.

Not applicable
Author

No we have to create flag colomn in script in that column that field has to have Early,Late,Over ,within