Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
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
Highlighted
Employee
Employee

Re: Set Analysis with column contents as *

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

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.