topic Re: Time Extraction in QlikView

Time Extraction

Anonymous — Mon, 05 May 2014 13:40:00 GMT

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

Re: Time Extraction

anbu1984 — Mon, 05 May 2014 13:42:56 GMT

Load PersonName,Min( TimeStamp) As InTime, Max( TimeStamp) As OutTime

From Src.xlsx

Group by PersonName

Re: Time Extraction

Anonymous — Tue, 06 May 2014 05:34:52 GMT

Anbu,

I am having multiple fields in that table, your expression is ok when you are having two fields say PersonName and TimeStamp.

What to do when I have 10+ fields in the same table?

Regards,

Balraj Ahlawat

Re: Time Extraction

Anonymous — Tue, 06 May 2014 05:38:13 GMT

Anbu,

One more thing I want to add, these two fields are into two separate tables.

Regards,

Balraj Ahlawat

Re: Time Extraction

SunilChauhan — Tue, 06 May 2014 05:38:42 GMT

take one more table which is resident to original and having above two field

Re: Time Extraction

kaushiknsolanki — Tue, 06 May 2014 05:40:40 GMT

Hi,

Just create a new field which identifies Intime and Outime, something like shown below.

Load *,if(right(TimeStamp) = 'PM','Out Time','In Time') as InOutFlag from xyz.

Now on qlikview sheet create a pivot table with all required fields and expression as only(TimeStamp)

Now drag the field (InOutFlag) on top of the TimeStamp expression and drop it there.

So now you will have the table with the format you want.

Regards,

Kaushik Solanki

Re: Time Extraction

Anonymous — Tue, 06 May 2014 05:44:54 GMT

Kaushik,

Some peoples are there who log in when time is into PM say 2nd half as well.

What is the solution for that?

Regards,

Balraj Ahlawat

Re: Time Extraction

anbu1984 — Tue, 06 May 2014 05:47:32 GMT

If you have more fields to select along with min and max values, then you can try analytic function like below

Load PersonName,Min( TimeStamp) Over(Partition by PersonName) As InTime, Max( TimeStamp) Over(Partition by PersonName) As OutTime, Fld2, Fld3...

From Table

How do you join Tables having times to your main table?

Re: Time Extraction

its_anandrjs — Tue, 06 May 2014 05:48:37 GMT

Load your table like below script and if not required Source table then drop the table at last

Source:

LOAD Time(TimeStamp,'hh:mm:ss TT') AS TimeStamp,PersonName;

LOAD * INLINE [

PersonName, TimeStamp

ABC, 8:35:02 AM

ABC, 5:32:04 PM

];

Final:

LOAD

PersonName,

Time( Min(TimeStamp)) AS Min

Resident Source

Group By PersonName;

Left Join

LOAD

PersonName,

Time(Max(TimeStamp)) AS Max

Resident Source

Group By PersonName;

Drop table Source; // If required drop this table.

Re: Time Extraction

kaushiknsolanki — Tue, 06 May 2014 05:49:13 GMT

HI,

How do you identify such transactions.

Regards,

Kaushik Solanki

Re: Time Extraction

Anonymous — Tue, 06 May 2014 05:49:26 GMT

Sunil,

Both the fields are into 2 different tables.

Can we resident two tables at one time?

Regards,

Balraj

Re: Time Extraction

SunilChauhan — Tue, 06 May 2014 05:52:10 GMT

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

Re: Time Extraction

Anonymous — Tue, 06 May 2014 06:06:41 GMT

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

Re: Time Extraction

kaushiknsolanki — Tue, 06 May 2014 06:09:24 GMT

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

Re: Time Extraction

Anonymous — Tue, 06 May 2014 06:09:47 GMT

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

Re: Time Extraction

SunilChauhan — Tue, 06 May 2014 06:21:47 GMT

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

that makes easy to helps for every one

Re: Time Extraction

Anonymous — Tue, 06 May 2014 06:40:05 GMT

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

Re: Time Extraction

Anonymous — Tue, 06 May 2014 06:42:52 GMT

Kaushik,

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

See, if any alternate possible?

Regards,

Balraj

Re: Time Extraction

SunilChauhan — Tue, 06 May 2014 06:44:59 GMT

use intervalmatch function

load *

from X1;

load * from X2;

intervalmatch(PunchTime)

load inTime,Outtime from X2;

hope this helps

Re: Time Extraction

kaushiknsolanki — Tue, 06 May 2014 06:45:34 GMT

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