Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table where the default value of many cells is an asterisk (*). In my set expression I'm trying to select on these values, but the resulting sum is totaling the entire selection - probably because the * is actually an operator of some description. I've attempted to place it inside quotes, eg
'*'
and "*"
both these result in the same end result - the entire set is summed, not just the columns with cells = *.
sum(
{
$<my_column_id={"*"}>
} us)
Any ideas how i can 'escape' * to mean "*"?
Thanks
Try this one: sum({$<my_column_id={"=my_column_id='*' "}>}us)
=count({<my_column_id={"=my_column_id=chr(42)"}>}Dim1)
Try this one: sum({$<my_column_id={"=my_column_id='*' "}>}us)
Thanks fellows - Clever Anjos, your response works also if I substitute the 'count' for 'sum'.
sorry, mistyped here, it was supposed to be a sum
Maybe you can mark my answer as "Helpfull"
You can also try below in your load script.
Replace(Fieldname, '*' , '|') as NewFieldname
Expression as
sum({<$<NewFieldname={"|"}>} us)
Definitely something to remember for the future - I have about 57 columns in my datasource, the above formulas are good workarounds but as the expressions get larger (I have a behemoth to write shortly), they will, i suspect, start to become somewhat hard to read.
While loading you can change the script as
Load Field1,
Replace(Field2,'*','|') as Field2
From source;
So you will have same number of columns (57). I just give you an idea. If you have one or two, it would be helpful.