hi, i'm trying to sum a column, whose values are alphanumeric
i.e. can have 0, 1 12, 100, n/a and TBC, how do i exclude the character fields so i can sum the numbers?
Hello @Craig_Harley ,
As you mentioned the Table Transformer macro of our Table Filter, Charts & Spreadsheets app, let me propose the following type of solution:
SELECT
SUM(T1.'Your column name'::number) AS 'Numeric Sum'
FROM T1
WHERE MATCH_REGEXP(T1.'Your column name', "^[0-9]+(.[0-9]+)?$")
hi Alexey, this works perfectly on the first of 4 columns I am trying to Sum using this approach, however when i apply to the 2nd column, it does not give the correct result. I am additionally rounding to 2 decimal places
the SQL i am using is
SELECT
'Sum Transaction Totals',
ROUND (SUM (T1.'Transactions1'::number), 2) AS 'Peak Load (TPS)',
ROUND (SUM(T1.'Transactions2'::number), 2) AS 'Peak Load (TPM)'
FROM T1
WHERE MATCH_REGEXP(T1.'Transactions1', "^[0-9]+(.[0-9]+)?$")
AND MATCH_REGEXP(T1.'Transactions2', "^[0-9]+(.[0-9]+)?$")
any thoughts?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello @Craig_Harley ,
The initially proposed approach is for the only column. This approach doesn't work correctly for multiple columns because it filters entire rows. If one column has a non-numeric value, the whole row is excluded, even if the other column has valid data. As a result, valid values are skipped, and the totals are incorrect.
Using CASE WHEN MATCH_REGEXP(...) THEN ... ELSE 0 inside each SUM should allow independent filtering per column, ensuring accurate aggregation. Please try this SQL query:
SELECT
'Sum Transaction Totals',
ROUND(SUM(
CASE
WHEN MATCH_REGEXP('Transactions1' + "", "^[0-9]+(\.[0-9]+)?$")
THEN CAST('Transactions1' AS number)
ELSE 0
END
), 2) AS 'Peak Load (TPS)',
ROUND(SUM(
CASE
WHEN MATCH_REGEXP('Transactions2' + "", "^[0-9]+(\.[0-9]+)?$")
THEN CAST('Transactions2' AS number)
ELSE 0
END
), 2) AS 'Peak Load (TPM)'
FROM T1
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
How would you see this, CQL is for text searching.
I can't see the sum option in using CQL
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
let me re-phrase, I am using SQL in table Transformer from Table Filter and charts
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi
To sumup, see this doc.
I don't think there are options to exclude.
https://docs.stiltsoft.com/tfac/cloud/calculations-in-tables-42239860.html
You could raise this as the app developer's support portal.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.