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

[anbu1984]

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

From Src.xlsx

Group by PersonName

[Anonymous]

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

[Anonymous]

Anbu,

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

Regards,

Balraj Ahlawat

[SunilChauhan]

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

[kaushiknsolanki]

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

[Anonymous]

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

[anbu1984]

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?

[its_anandrjs]

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.

[kaushiknsolanki]

HI,

     How do you identify such transactions.

Regards,

Kaushik Solanki