Time Extraction

[Anonymous]

Dear all,

I am having a field say TimeStamp which stores intime(AM) and outime(PM) of a person.

Sample:   PersonName  TimeStamp

                  ABC             8:35:02 AM

                  ABC             5:32:04 PM

I want to represent TimeStamp filed into two fields say InTime and OutTime w.r.t person.

Format:          PersonName        InTime            OutTime

                       ABC              8:35:02 AM          5:32:04 PM

Reply me ASAP.

Regards,

Balraj Ahlawat

[Anonymous]

Sunil,

Both the fields are into 2 different tables.

Can we resident two tables at one time?

Regards,

Balraj

[SunilChauhan]

you should  clarify the situation @ start

no we cant take resident from two tables

we can take two seperate resident for that

and combine them into one based on some key field

[Anonymous]

Kaushik,

I have seen in the data, people are there who logged in at 12.30 PM and logged out at 8.30 PM.

It is in the data only.

Regards,

Balraj

[kaushiknsolanki]

Dear Balraj,

     I understand that you can look at the data and make out that this is an evening shift, but there has to be some way for QlikView to understand same.

     Meaning there should be some flag which specifies that this is an evening shift where PM is the start entry and AM is out entry.

Regards,.

Kaushik Solanki

[Anonymous]

Sure Sunil,

It was my mistake, I didn't share at the start.

But this is the scenario I am having,  see if can suggest any solution.

Regards,

Balraj

[SunilChauhan]

better if you can describe what are the field available in two tables

that makes easy to helps for every one

[Anonymous]

Dear Kaushik,

There is no evening shift...Let me tell you complete scenario from start.

I am having two table say X1 & X2 with multiple fields. In X1 table I am having one field PersonName and in X2 table one field is PunchTime and they are connected through a field say PersonID.

Now data is stored like this in X2:

PersonID         PunchTime

100001        5/6/2014  8.48 AM

100001        5/6/2014  5.49 PM

100001        5/5/2014  12.20 PM

100001        5/5/2014   7.30 PM 

There is no rotational shift, its a general shift. Some of the person have punched in 2nd half as well. Same thing is repeated whole month/year etc.

Now I want to display like this:

PersonName     PersonID          InTime                           OutTime

ABC                  100001          5/6/2014 8.48 AM         5/6/2014    5.49 PM

ABC                   100001         5/5/2014 12.20 PM       5/5/2014    7.30 PM

I hope, it will help you to understand completely.

Regards,

Balraj

[Anonymous]

Kaushik,

Flag is here but it's not properly maintained and its not working with this problem.

See, if any alternate possible?

Regards,

Balraj

[SunilChauhan]

use intervalmatch function

load *

from X1;

load * from X2;

intervalmatch(PunchTime)

load inTime,Outtime from X2;

hope this helps

[kaushiknsolanki]

Hi,

Then you will have to find the minimum time for a day and maximum time for a day.

Try this.

Load Person_ID,Min(Timestamp) as InTime, Max(Timestamp) as outtime resident xyz

group by Person_ID,date(floor(Timestamp));

Regards,

Kaushik Solanki