Preceding load

[#1]

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?

[joshabbott]

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

[#3]

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.

[#4]

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;

[veeranj · Accepted solution]

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

[#6]

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!