topic Re: How to remove null string values in the expression? in QlikView

How to remove null string values in the expression?

Tue, 07 Oct 2014 09:42:45 GMT

Hi everyone,

I have created an expression =MaxString(Supplier) in my straight table but some of rows are blank.

If i used Supplier in the Dimension, I could use Suppress when value is Null option but i want do the same in the expression.

I would be really grateful if you advise me how to fix this issue.

Best

Robert

Re: How to remove null string values in the expression?

PrashantSangle — Tue, 07 Oct 2014 09:45:56 GMT

Hi,

Use isnull()

if(not isnull(Supplier),MaxString(Supplier))

Regards

Re: How to remove null string values in the expression?

Anonymous — Tue, 07 Oct 2014 09:46:14 GMT

Robert,

Try to use a calculated dimension:

=if(len(Suplier)>0, Suplier)

and check Supress when value is null.

Marc.

Re: How to remove null string values in the expression?

alexandros17 — Tue, 07 Oct 2014 09:47:41 GMT

Try something like:

If(Trim(MaxString(Supplier))='',0,MaxString(Supplier))

then check suppress zero value in presentation tab

let me know ...

Re: How to remove null string values in the expression?

Gysbert_Wassenaar — Tue, 07 Oct 2014 09:48:25 GMT

If the values were really nulls then Suppress When Value is Null would suppress them. Nulls cannot be selected, so if you can select such a value you know it's not a real null. You can replace the spaces with something else in the script. You can check for such values with something like if(len(trim(MyField))=0, 'No Value', MyField) as MyField.

Re: How to remove null string values in the expression?

anbu1984 — Tue, 07 Oct 2014 09:52:16 GMT

Check this qvw

Re: How to remove null string values in the expression?

Tue, 07 Oct 2014 11:20:53 GMT

Hello everyone,

Thanks for your reply.

I just used the Mr.Anbu Sample qvw file, he given my expected answer but now i added one more attribute(Target) in the data and added sum(Target) expression in table then i want to remove the null value of supplier So, i tried your advised way but it doesn't work.

I want to remove the red color marked data and attached the Mr.Anbu qvw file.

Re: Re: How to remove null string values in the expression?

senpradip007 — Tue, 07 Oct 2014 11:27:00 GMT

PFA. Hope it will meet your requirement.

Re: Re: How to remove null string values in the expression?

anbu1984 — Tue, 07 Oct 2014 11:29:14 GMT

Check this

Re: How to remove null string values in the expression?

Tue, 07 Oct 2014 11:49:23 GMT

Its works.

Thanks everyone....

Many Thanks Mr.Anbu and Mr.Pradip.

Re: Re: How to remove null string values in the expression?

senpradip007 — Tue, 07 Oct 2014 11:53:57 GMT

Most welcome...

Re: How to remove null string values in the expression?

anbu1984 — Tue, 07 Oct 2014 11:55:31 GMT

From "QV 11 for Developers" Book

A word on calculated dimensions

As useful as they are, calculated dimensions (such as the one we created earlier) are

not performance-friendly. Besides delaying calculation time, they can sometimes

prevent a chart's state from being cached to RAM, hence stopping QlikView's

caching algorithm from coming into play.

As calculated dimensions are sometimes necessary for advanced aggregations, it

is advisable to use this feature only when there is no other way of accomplishing

certain visualizations. Whenever a calculated dimension can be created as a new

field from the script, it is advisable to do so in order to use it in a more natural way

in chart objects

Re: How to remove null string values in the expression?

Tue, 07 Oct 2014 11:59:30 GMT

Hi Anbu,

Thanks for advise.

I have more than 25 metrics in my report so better i would go calculated dimension.

But I really learned from =If(Not(IsNull(MaxStr)),sum(Target)) your expression as i don't know before that we could use expression label for another expression.

Best

Robert