topic Re: Re: Create a custom table from provided data in QlikView

Create a custom table from provided data

muhammadraza — Tue, 15 Apr 2014 13:03:19 GMT

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

Re: Create a custom table from provided data

its_anandrjs — Tue, 15 Apr 2014 13:11:07 GMT

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

Re: Create a custom table from provided data

alexpanjhc — Tue, 15 Apr 2014 13:45:50 GMT

Try this:

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.

Re: Re: Create a custom table from provided data

jagannalla — Tue, 15 Apr 2014 14:05:41 GMT

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