1

Using XSLT v1.0

tl;dr : How do i count the number of rows where two columns contain non-null values?

I have an application that records time stamps. I am taking two of those time stamps columns and finding the difference between the two times for each row. In some rows, there will not be a time stamp recorded for a column. This means that I may have only x amount of rows with both time stamps. I take all the time stamp differences, convert them to seconds and then add them up. I then want to divide them by the number of rows that have bot timestamps to get the average time.

I need a way to count only the rows that have valid time stamps in two columns.

I can count each row using :

<xsl:variable name="numRegOutNodes" select="count(/dsQueryResponse/Rows/Row/@Registration_OUT[string(.)])" />

And I can then use that to get an average usign this:

<xsl:value-of select="format-number((($sum div $numRegOutNodes) div 60), &quot;#,##0.00;-#,##0.00&quot;)"/>

But that ONLY is dividing by the number of rows that Registration_OUT is not null. I need the number of rows where Registration_OUT and Registration_IN are not null.

For example

<xsl:variable name="numRegNodes" select="count(rows where registration_out and registration_in are not null)" />

Update

I attempted to use the following :

 <xsl:variable name="numRegOutNodes" select="count(/dsQueryResponse/Rows/Row/@Registration_OUT[string(.)] & /dsQueryResponse/Rows/Row/@Registration_IN[string(.)])" />

per Vadims instruction. This did not yield any results and raised the question: is & an acceptable operator in XSLT? A question for another day I suppose.

Next I used :

Test : <xsl:variable name="numRegOutNodes" select="count(/dsQueryResponse/Rows/Row/@Registration_OUT[string(.)] and /dsQueryResponse/Rows/Row/@Registration_IN[string(.)])" />

No success.

I then used :

Test : <xsl:variable name="numRegOutNodes" select="count(/dsQueryResponse/Rows/Row/@Registration_OUT[string(.)] | /dsQueryResponse/Rows/Row/@Registration_IN[string(.)])" />

Which did not error out the webpart - and quite possibly could work. But why would the or operator work and not the and operator?

Thanks for paying attention. cheers

Thank you!

1

The following expressions could be specified in order to get the number of rows where:

a) Registration_OUT AND Registration_IN are not null:

 <xsl:param name="numRegOutNodes" select="count(/dsQueryResponse/Rows/Row[@Registration_OUT[string(.)] and @Registration_IN[string(.)]])"/>

b) Registration_OUT OR Registration_IN are not null:

<xsl:param name="numRegOutNodes" select="count(/dsQueryResponse/Rows/Row[@Registration_OUT[string(.)] or @Registration_IN[string(.)]])"/>

P.S. Code samples have been tested in SPS 2010

  • This was my original attempt, although I used "and" as opposed to "&". Either way fails to work - both cause a correlation ID to appear. Thanks for trying. – SynchronizeYourDogma Nov 26 '13 at 14:57
  • Seppuku, the answer has been updated – Vadim Gremyachev Nov 30 '13 at 23:08
0

The following should work. You have to play with the code to get it work. just make sure it's formatted this way column_Name[text()]

count(registration_out[text()])

  • Thanks for the reply, I appreciate it. However the question is really about how to format a count for all rows that are empty for BOTH Registration_OUT and Registration_IN at the same time. I can get counts for both of them individually no problem. For example : count(number of rows where registration_out OR registration_in are not empty). I've tried quite a few different permutations and haven't made any progress. Thank you for your time, I truly appreciate it. – SynchronizeYourDogma Nov 26 '13 at 1:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.