topic Re: Preceding load in QlikView

Preceding load

Fri, 08 Aug 2014 12:39:00 GMT

Hello,

I have 2 fields in a table. Dept ID and Emp ID. If I have to pull employees (Not Employee ID's) under each dept who are > than 5.

How can I do this in qlikview and sql?

Re: Preceding load

joshabbott — Fri, 08 Aug 2014 12:42:48 GMT

I'm not understanding your question.

#1 - You have a table in your data load with two fields, Dept ID and Emp ID.

#2 - You have to pull employees from where?

Please clarify

Re: Preceding load

Fri, 08 Aug 2014 12:47:24 GMT

Sorry, Let me make it clear

TableA:

Dept ID,

Emp ID;

I am just having these two fields. And I want employees more than 5 in each Dept. In sql we have to use group by and having clause. In QV we need to do preceding load. But I need the actual Code.

Re: Preceding load

Fri, 08 Aug 2014 13:01:48 GMT

LOAD VCOUNT WHERE VCOUNT > 5;

Load count(EMPNO) AS VCOUNT group by DEPTNO;

LOAD EMPNO,

ENAME,

JOB,

MGR,

HIREDATE,

SAL,

COMM,

DEPTNO,

"DATE_UPDATED";

SQL SELECT *

FROM SCOTT.EMP;

Re: Preceding load

veeranj — Fri, 08 Aug 2014 13:08:52 GMT

HI,

First Count the no.of employees in a dept and then use preceeding load for that.

load * where count>=5;

load count(empid) as count,deptid group by deptid ;

data:

LOAD * INLINE [

empid, deptid

10, d1

20, d1

30, d1

40, d1

50, d1

60, d1

70, d2

80, d2

90, d2

100, d3

123, d3

456, d4

698, d4

356, d4

9874, d4

368, d4

354789, d5

8245698, d6

123654, d6

456963, d6

1236, d6

45698, d6

];

Thanks,

Anjee

Re: Preceding load

Fri, 08 Aug 2014 13:44:24 GMT

Hi Kailash,

I'm assuming you have loaded the employee details before creating the Table A. If this is the case you may be able to use the 'Resident' keyword when loading the data into Table A.

eg:

TableA:

Load

DeptID,

EmpID

Resident <Employees_Table>

where [Criteria] > 5

Hope this helps!