Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

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
sunny_talwar

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

jagan
Partner - Champion III
Partner - Champion III

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.