Need help writing the IQL

[Dean]

Hi Folks, 

I could use some help in writing the Insight IQL to get a specific result and am banging my head against the wall as nothing I come up with seems to work. I am sure its got to be basic and as soon as someone shows me I will see what I am missing. 

I am trying to get a numeric total for all employees that have a certain skill. So if you see the pic provided, you will see I have a list of skills that my staff can have (for instance Ansible 1, Ansible 2 and you can see I have an Inbound Reference showing 7 employees rate themselves at that level)

So in essence I am looking for a list that looks something like this:

Ansible 1 - 7

Ansible 2 - 5    etc etc. 

The IQL I "think" should be something like 

Objecttype = Knowledge AND object HAVING inR(Employee)  

But I get nothing. Please if anyone can offer a suggestion, I would be in your debt. 

Thanks so much for your time.

 

[Nader Nassif · Accepted solution]

Hi Dean,

You can get the sum of some attribute values together to appear in a sum column when viewing your objects as list. But what you want to achieve should be much easier.

If you view the objectType Employee instead of Knowledge. Assuming that the attribute name is "Skill" , then the correct IQL you are looking for is 

Skill = "Ansible (Level 1)"

This IQL will list all your employees that have this skill. Looking at the list view will show you the sum of your employees that have this skill. 

For each skill level there should be a different IQL for Insight to read your data and do this calculation for you.

You can use this IQL with Insight Reports ,even with Insight Custom Fields if you wish to list those employees in a certain workflow.

Cheers!

Nader

[Dean]

Hi Nader, 

As always you are the bomb, But if I can take this one further, you mentioned at the beginning of your reply as to specifically what I want to do. 

"You can get the sum of some attribute values together to appear in a sum column when viewing your objects as list. But what you want to achieve should be much easier."

How would I do this? I want to take all my Skills and list the numeric number of how many employees are tied to each skills- So in essence:

Ansible (Level 1) - 7 

Ansible (Level 2) - 5 

so the 7 and the 5 are the number of employees that have that skill level. You IQL works for each individual skill, but I would like to pipe out the whole list.

Thanks again!

[Nader Nassif]

Hi Dean,

Thank you for that ;)

Your current data structure will not work with the out of the box sum option. The reason is the summation function exists only for int or float data type attribute. This means that if you put the number 1 for example on attribute "Ansible- Level 1" on User X, and then for the rest of the users then Insight can calculate this sum for you as explained on the documentation page.

With your data structure, you can achieve the desired result using :

1. Get the reference info from your object (since it contains this sum) using REST /rest/insight/1.0/object/{id}/referenceinfo
2. The data will exist on numberOfReferencedObjects
3. Save this info locally in a JSON file and Send this info using REST to your objectType "Knowledge" to update Attribute called "Total knowledgable employees" which will hold this sum. Or setup a JSON import to import the generated file using the REST API and only add this attribute to be updated.

Cheers!

Nader

[Dean]

Thanks Nader! I appreciate your time and help on this.