Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
pzwonford
New 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?

Tags (2)
1 Solution

Accepted Solutions

Re: countif in load script

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

2 Replies

Re: countif in load script

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

MVP
MVP

Re: countif in load script

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.

Community Browser