How to find the no. of employees department wise?

Question

Lets say I have n documents whose document structure looks like this-

<employee>  <name/>     <dob/>  .....   <dept>dept1</dept> </employee>
<employee>  <name/>     <dob/>  .....   <dept>dept11</dept> </employee>
<employee>  <name/>     <dob/>  .....   <dept>dept12</dept> </employee>
<employee>  <name/>     <dob/>  .....   <dept>dep13</dept> </employee>  
<employee>  <name/>     <dob/>  .....   <dept>dep1n</dept> </employee>  

<employee>  <name/>     <dob/>  .....   <dept>dept2</dept> </employee>
<employee>  <name/>     <dob/>  .....   <dept>dept21</dept> </employee>
<employee>  <name/>     <dob/>  .....   <dept>dept22</dept> </employee>
<employee>  <name/>     <dob/>  .....   <dept>dept23</dept> </employee>  
<employee>  <name/>     <dob/>  .....   <dept>dept2n</dept> </employee>  

<employee>  <name/>     <dob/>  .....   <dept>deptn</dept> </employee>
<employee>  <name/>     <dob/>  .....   <dept>deptn1</dept> </employee>
<employee>  <name/>     <dob/>  .....   <dept>deptn2</dept> </employee>
<employee>  <name/>     <dob/>  .....   <dept>deptn3</dept> </employee>  
<employee>  <name/>     <dob/>  .....   <dept>deptnn</dept> </employee>

And dept1 is the parent. i.e. All dept with name like dept11,dept12,dept13...are subset of dept1. Similary dept2 is the parent and all dept with the name like dept21,dept22,dept23 are subset of dept2.
I want to find the list of employees who belong to the main deptartment i.e. list of employees who belong to dept1,dept11,dept12 & so on, with the help of Xquery and Marklogic API's.
Hope you all understood the question.

If the amount of departments less than 10 (else i don't understand how interpret 111 - 11-1 or 1-11), with Xath 1.0 you can for each department get `count(//employee//dept[starts-with(.,'dept2')])` — splash58, May 13 '17 at 06:26
the document count is n and there is no - symbol used. Its 11, 12, 13 etc. — bosari, May 13 '17 at 06:53
@rishabhv66 The question was, if there are more then 10 departments, what would 'dept111' mean? *child no 1 of parent 'dept11'* or *child no 11 of parent 'dept1'*? — har07, May 13 '17 at 09:07
From my example it's clear there is no such thing as 111. Maximum it will be 1n where n is a number from [0-9]. — bosari, May 13 '17 at 09:35

score 3 · Answer 1 · answered May 13 '17 at 11:49

A solution that would scale better would make use of a range index on dept, a lexicon lookup with cts:values to get the unique values of dept with their frequencies. And then an ordinary loop through the values to aggregate frequencies with the parent department. Perhaps something like:

let $aggregates := map:map()
let $_ :=
  for $dept in cts:values(cts:element-reference(xs:QName("dept")))
  let $freq := cts:frequency($dept)
  let $parent := substring($dept, 1, 5)
  return map:put($aggregates, $parent, (map:get($aggregates, $parent), 0)[1] + $freq)
return $aggregates

To make it even easier, you could consider making parent department explicit by changing your XML to something like:

<dept>dept1</dept><sub-dept>dept11</sub-dept>

That way a cts:values on dept would immediately give the accurate frequency counts of the top-level departments, and a cts:values on sub-dept would give the same for sub-departments.

HTH!

could you explain what going on here - map:get($aggregates, $parent), 0)[1] + $freq) ? — bosari, May 13 '17 at 13:40
`(map:get($aggregates, $parent), 0)[1]` looks if a value is known for key `$parent`. If not, it selects 0. In other languages you could write `map:get($aggregates, $parent) || 0`, but unfortunately not in XQuery. The `+ $freq`, just adds the frequency of the current dept to that of the parent, which gets saved using the `map:put`.. — grtjn, May 13 '17 at 13:44

score 0 · Answer 2 · answered May 13 '17 at 09:38

For now I have found a solution. There can be a better way to achieve this, I am sure.

let $result := for $x in /doc/employee/dept[fn:starts-with(., "dept")] order by $x descending
                return $x
let $max := fn:codepoints-to-string(fn:string-to-codepoints($result[1])[5])
let $res := for $x in (1 to xs:integer($max))
              let $count := fn:count(/doc/employee/dept[fn:starts-with(., fn:concat("dept",xs:string($x)))])
              return (<doc><dept>dept{$x}</dept><count>{$count}</count></doc>)
return $res

The result would be like for example-

<doc>
  <dept>dept1</dept>
  <count>3</count>
</doc>
<doc>
  <dept>dept2</dept>
  <count>2</count>
</doc>
<doc>
  <dept>dept3</dept>
  <count>1</count>
</doc>

How to find the no. of employees department wise?

2 Answers2