Create a custom table from provided data

[muhammadraza]

Hello Guys,

I have the following data

I want to create the following custom table in the load statement from the above table,

Achieving the result on front end is also ok but back-end is the priority.

Please guide how can I achieve this.

Regards,

Muhammad Raza

[its_anandrjs]

1.First load only "IN" data from raw and with key combination.

2.Second load "Out" data and left join the table with IN table with MAX Date value with key combination.

3.Last load only max date value and then join this with the primary table to get all data drop if key is not required

Hope this helps

[alexpanjhc]

Try this:

IN

select date as DateIN, employee as EmployeeIN, timeIN,location as [location IN], data&employee

from Data

where Location like '%IN';

left join(IN)

OUT

select date as DateOut, employee as Employeeout, max(timeIN) as [time out],location as [location out], data&employee

from Data

where Location like '%OUT';

group by date, employee, location

something like this should help you to build a new table.

[jagannalla]

Hi,

Hope it helps you.

Temp:

LOAD *,SubStringCount(LocationIn,'In') as In;

LOAD * INLINE [

    Date, Emp, TimeIn, LocationIn

    20140401, Emp1, 6:30, 2nd floor In

    20140401, Emp1, 7:30, 2n floor Out

    20140401, Emp1, 7:40, Main In

    20140401, Emp1, 7:50, Main Out

    20140401, Emp2, 8:30, Main In

    20140401, Emp2, 9:40, Main Out

    20140401, Emp2, 10:30, 2nd floor In

    20140401, Emp2, 11:00, 2n floor Out

];

Join

LOAD TimeIn,LocationIn, Peek(TimeIn) as TimeOut,Peek(LocationIn) as LocationOut Resident Temp Order By Emp,TimeIn desc;

Main:

NoConcatenate

LOAD * Resident Temp

Where In =1

;

DROP Table Temp;

DROP Field In;

Cheers!!

Jagan