Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

countif in load script

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

TitleForename1employee numbercount of employee number
MrBob12341
MrSid12341
MrJohn12341
MrEd44441

What i want it to show is

TitleForename1employee numbercount of employee number
MrBob12343
MSid12343
MrJohn12343
MrEd44441

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?

1 Solution

Accepted Solutions
Highlighted

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];

View solution in original post

2 Replies
Highlighted

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];

View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

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.