topic Set Analysis with column contents as * in QlikView

Set Analysis with column contents as *

Fri, 27 Nov 2015 14:41:02 GMT

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

Re: Set Analysis with column contents as *

Clever_Anjos — Fri, 27 Nov 2015 15:06:04 GMT

=count({<my_column_id={"=my_column_id=chr(42)"}>}Dim1)

Re: Set Analysis with column contents as *

Gysbert_Wassenaar — Fri, 27 Nov 2015 15:10:58 GMT

Try this one: sum({$<my_column_id={"=my_column_id='*' "}>}us)

Re: Set Analysis with column contents as *

Fri, 27 Nov 2015 15:19:51 GMT

Thanks fellows - Clever Anjos, your response works also if I substitute the 'count' for 'sum'.

Re: Set Analysis with column contents as *

Clever_Anjos — Fri, 27 Nov 2015 15:34:29 GMT

sorry, mistyped here, it was supposed to be a sum

Re: Set Analysis with column contents as *

Clever_Anjos — Fri, 27 Nov 2015 15:38:46 GMT

Maybe you can mark my answer as "Helpfull"

Re: Set Analysis with column contents as *

tamilarasu — Fri, 27 Nov 2015 15:50:22 GMT

You can also try below in your load script.

Replace(Fieldname, '*' , '|') as NewFieldname

Expression as

sum({<$<NewFieldname={"|"}>} us)

Re: Set Analysis with column contents as *

Fri, 27 Nov 2015 16:45:04 GMT

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.

Re: Set Analysis with column contents as *

tamilarasu — Fri, 27 Nov 2015 16:57:01 GMT

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.