Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Create a custom table from provided data

Hello Guys,

I have the following data

orignal source.png

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

custom table.png

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

3 Replies
Highlighted

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

Highlighted
Specialist
Specialist

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.

Highlighted
Partner
Partner

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