Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Set Analysis with column contents as *

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

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Set Analysis with column contents as *

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


talk is cheap, supply exceeds demand
8 Replies
Employee
Employee

Re: Set Analysis with column contents as *

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Set Analysis with column contents as *

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


talk is cheap, supply exceeds demand
Not applicable

Re: Set Analysis with column contents as *

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

Employee
Employee

Re: Set Analysis with column contents as *

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

Employee
Employee

Re: Set Analysis with column contents as *

Maybe you can mark my answer as "Helpfull"

Re: Set Analysis with column contents as *

You can also try below in your load script.

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

Expression as

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

Not applicable

Re: Set Analysis with column contents as *

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 *

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.