topic countif in load script in QlikView

countif in load script

Anonymous — Thu, 14 Apr 2016 15:37:25 GMT

hi guys. I have the following load script

Load

title as Title,

[forename 1] as Forename1,

[Employee] as Employee number

From [c:\test.xlsx (ooxml, embedded labels, Table is 'Test');

This will import individual information.

I also need to run a check which looks down the entire table and identifies employee numbers which appear more than once but when I do this as above it shows

Title	Forename1	employee number	count of employee number
Mr	Bob	1234	1
Mr	Sid	1234	1
Mr	John	1234	1
Mr	Ed	4444	1

What i want it to show is

Title	Forename1	employee number	count of employee number
Mr	Bob	1234	3
M	Sid	1234	3
Mr	John	1234	3
Mr	Ed	4444	1

I think you could do this with some kind of countif in the load script but i've searched and i cannot seem to get it. Any suggestions?

Re: countif in load script

sunny_talwar — Thu, 14 Apr 2016 15:40:36 GMT

Try this:

Table:

LOAD

title as Title,

[forename 1] as Forename1,

[Employee] as [Employee number]

From ,

Count([Employee number]) as Count

Resident Table

Group By [Employee number];

Re: countif in load script

jagan — Thu, 14 Apr 2016 16:09:52 GMT

Hi,

You can also try like this

Data:

Load

title as Title,

[forename 1] as Forename1,

[Employee] as Employee number,

If(Employee = Peek(Employee), RangeSum(Peek(Emp_Count), 1), 1) AS Emp_Count

From [c:\test.xlsx (ooxml, embedded labels, Table is 'Test')

ORDER BY Employee;

Hope this helps you.

Regards,

Jagan.