countif in load script

[Anonymous]

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?

[sunny_talwar · Accepted solution]

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]

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.