Hello,
I find out the following behaviour.
Cannot really say if it's a bug or not, but it is not that straight forward.
Here is the case.
A table with 3 columns Area / Activity / Status.
Status column contains a percentage "as text" or NA : 0%, 25%... NA
Out of this table I am trying to compute a percentage of completion per Area excluding the NA (some Activities are not relevant).
I am using the following query (probably not the most efficient one, but might be treated in another topic probably)
SELECT Area, (Sum(Status) / (100 * COUNT(Activity)))*100 AS 'Completed', (1 - Sum(Status) / (100 * COUNT(Activity)))*100 AS 'Remaining'
FROM T*
WHERE Status >= 0
GROUP BY Area
If the first non-empty row of my input table contains the NA value for the Activity, the query returns No Row.
Ex:
INPUT TABLE
AREA / Activity / Status
area1 / act1 /
area1 / act2 / NA
area1 / act3 / 10%
area1 / act4 / NA
Result : No row
Expected:
AREA / Completed / Remaining
area1 / 10 / 90
If, on the other hand the first non-empty row contains a value (in percentage), it works fine. Computing the completion percentage.
Ex:
INPUT TABLE
AREA / Activity / Status
area1 / act1 /
area1 / act2 / 100%
area1 / act3 / 10%
area1 / act4 / NA
Result:
AREA / Completed / Remaining
area1 / 55 / 45
Thank you for your help in advance
Regards
Cédric
