Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
I have a main column i want to count msg, i want to filter other columns msg_del = null(can take X or null as values),
and i also want to filter msg_from when its not equal to "customer"
So far, my scripts works if i runt them separately, i tried merging them but it doesn't give me the correct numbers.
it would nice to write something as simple as:
SELECT COUNT(msg) FROM table1
WHERE msg_del is null AND
msg_from != "customer"
Also, isn't there a way to make my scripts a bit shorter and easier?
COUNT({<msg =E({$<msg_del = {X}>} msg)> }msg)
COUNT({<msg =E({$<msg_from = {customer} >} msg)> }msg)
Well i found the solution to be:
COUNT({<msg =E({$<msg_from = {customer} >}) * E({<msg_del = {X}>} )> }msg)
Using the E() modifier i'm excluding "customer" from msg_from, this can contain null values.
Using E() modifier i'm excluding "X" from msg_del, this can contain X or null as values.
It's not quite clear what do you want to achieve. I assume it's in some way a counting respectively considering of NULL. That's not possible in a direct way because NULL isn't stored in any way and the indirect ways could become quite tricky. Usually is the most practicable approach to replace the NULL with real values during the load with something like: if(len(trim(Field), Field, 'NULL') as Field.
With it your expression may look like:
COUNT({< msg_from -= {'customer'}, msg_del = {'NULL'}>} msg)
Well i found the solution to be:
COUNT({<msg =E({$<msg_from = {customer} >}) * E({<msg_del = {X}>} )> }msg)
Using the E() modifier i'm excluding "customer" from msg_from, this can contain null values.
Using E() modifier i'm excluding "X" from msg_del, this can contain X or null as values.