topic Re: How to merge records from two different fields from same table in QlikView

How to merge records from two different fields from same table

Mon, 05 Oct 2015 04:47:53 GMT

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

Re: How to merge records from two different fields from same table

SreeniJD — Mon, 05 Oct 2015 05:00:30 GMT

Hi Hemeswara Reddy

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

HTH

Sreeni

Re: How to merge records from two different fields from same table

Mon, 05 Oct 2015 05:18:37 GMT

i attached the data please send your reply

Re: How to merge records from two different fields from same table

jonathandienst — Mon, 05 Oct 2015 05:29:36 GMT

Like this:

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

Re: How to merge records from two different fields from same table

kathir — Mon, 05 Oct 2015 05:35:48 GMT

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

Re: How to merge records from two different fields from same table

Mon, 05 Oct 2015 05:46:08 GMT

By using this i am getting

Flag

Early/over

Early/within

Late/over

but i want

Flag

Early

Late

Over

Within

Re: How to merge records from two different fields from same table

jonathandienst — Mon, 05 Oct 2015 05:50:36 GMT

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

Re: How to merge records from two different fields from same table

jonathandienst — Mon, 05 Oct 2015 05:52:02 GMT

Re: How to merge records from two different fields from same table

Mon, 05 Oct 2015 06:41:43 GMT

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.

Re: How to merge records from two different fields from same table

Mon, 05 Oct 2015 07:03:05 GMT

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

Re: How to merge records from two different fields from same table

PrashantSangle — Mon, 05 Oct 2015 07:08:30 GMT

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

Re: How to merge records from two different fields from same table

Anonymous — Mon, 05 Oct 2015 07:09:27 GMT

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.

Re: How to merge records from two different fields from same table

sasiparupudi1 — Mon, 05 Oct 2015 07:33:43 GMT

What are the values in the variables

$(vEarlyTime) and $(vOverTime)

Re: How to merge records from two different fields from same table

Mon, 05 Oct 2015 07:48:53 GMT

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

set vOverTime=16/24;

Re: How to merge records from two different fields from same table

PrashantSangle — Mon, 05 Oct 2015 07:53:52 GMT

hi,

Did you try my suggetsion??

Regards

Re: How to merge records from two different fields from same table

Mon, 05 Oct 2015 07:56:01 GMT

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)

Re: How to merge records from two different fields from same table

Anonymous — Mon, 05 Oct 2015 07:59:13 GMT

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.

Re: How to merge records from two different fields from same table

sasiparupudi1 — Mon, 05 Oct 2015 08:00:48 GMT

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

Re: How to merge records from two different fields from same table

Mon, 05 Oct 2015 08:06:17 GMT

Hi,

Please find the attached QVW. !

Re: How to merge records from two different fields from same table

Mon, 05 Oct 2015 09:54:17 GMT

i dont have licensed version plz share script