Calculate quantity of employees whose name contains at least 2 letters 'n'

[Akash_Dixit]

Hi All,

I'm recently working on a case where I need to calculate the number of employees whose FIRST_NAME contains at least 2 letter 'n'.

I trying Set analysis approach using the substring count function,  data has names like below 

Anthony, Nandita, Jennifer etc. 

I tried in different ways but i can't find a solution maybe substring count isn't the right function for my purpose.

Thank you

Akash

[Akash_Dixit]

The following expression gives me the required outcome, I would appreciate it if someone could suggest a more effective approach or expression.

count(
{<FIRST_NAME = {"=SubStringCount(FIRST_NAME,'n') >= 2"}>
+<FIRST_NAME = {"=SubStringCount(FIRST_NAME,'N') >= 1"}>
}
EMPLOYEE_ID)

[BrunPierre · Accepted solution]

Hi,

Add this line to the load statement.

SubStringCount(Upper(FIRST_NAME),'N')>= 2 as NFlag

Then, use this as a fronted expression.

Count({<NFlag={'-1'}>} EMPLOYER_ID)

[Akash_Dixit]

I appreciate your comment; the logic makes sense and the expression is cleaner and less cluttered.

[stevedark]

Hi @Akash_Dixit 

It depends on how flexible this needs to be. It is always better to do work in the load script rather than the front end, so you could do this in the load:

Employee:
LOAD
    1 as EmployeeCount,
    FIRST_NAME,
    len(keepchar(lower(FIRST_NAME), 'n')) as NoNs,

And then your expression can be:

sum({<NoNs*={">=2"}>}EmployeeCount)

This will allow you to change how many ns you want to count with a variable. If it is always two, you can do this in the load:

Employee:
LOAD
    Firstname,
    if(len(keepchar(lower(FIRST_NAME), 'n')) >= 2, 1, 0) as [2orMoreNs],

And then the expression is simply:

sum([2orMoreNs])

If you would rather do nothing in the front end you can do:

sum(if(len(keepchar(lower(FIRST_NAME), 'n')) >= 2, 1, 0))

Hopefully this gives you some ideas.

Steve

[vinieme12 · Accepted solution]

just stick to either upper or lower case

=count({<FIRST_NAME = {"=SubStringCount(lower(FIRST_NAME),'n') >= 2"}>}EMPLOYEE_ID)