topic Re: Convert Null Values into String or Int in QlikView

Convert Null Values into String or Int

Tue, 29 Dec 2015 17:32:26 GMT

Hello,

I'm new to QlikView and this is a problem that might be easy for most of you but I've spent 5 hours trying to solve this.

I've searched for this but can't find a solution for my case.

Basically I need to Suppress All rows that have values. In other words, I just want the Null's of that Column.

My solution for this problem was to create a calculated dimension that changes the values (transforms what is Null into a string for example and the non-Nulls into nulls) and then click Suppress When Value is Null.

For that I've used this expression:

if( IsNull(Invoice.InternalDoc), 'NA', null() )

But the result is not what I expected.

Here's a what I got and what was I expecting:

ID DIM Expected Result Actual Result

CreatedDIM CreatedDIM

----- ----------- ------------------ ------------------

1 NULL NA NULL

2 NULL NA NULL

3 123 NULL NULL

4 321 NULL NULL

Where DIM is the Dimension that exists now, and CreatedDIM the one I created now to invert Values.

Once I want for the Straight Table to only show ID 1 and 2.

P.S. I can't make any changes to the data model, so I need to solve this directly in this report.

If anyone has an answer to this I would appreciate very much

Mensagem editada por: Moreira Carlos Attached sample

Re: Convert Null Values into String or Int

sunny_talwar — Tue, 29 Dec 2015 17:51:19 GMT

May be there is white space in your data for Dim. Try this:

If(Len(Trim(Invoice.InternalDoc)) = 0, 'NA', Null())

Re: Convert Null Values into String or Int

Tue, 29 Dec 2015 17:53:50 GMT

Hello,

thanks for your reply.

I've tried your expression, but the result is the same

Re: Convert Null Values into String or Int

sunny_talwar — Tue, 29 Dec 2015 18:35:37 GMT

Would you be able to share a small sample where it isn't working for you?

Re: Convert Null Values into String or Int

Thu, 31 Dec 2015 09:40:54 GMT

Try this if (Left(Invoice.InternalDoc,1)='', 'NA', null() )

Re: Convert Null Values into String or Int

sunny_talwar — Mon, 04 Jan 2016 11:52:12 GMT

Is this what you wanted?

Re: Convert Null Values into String or Int

Mon, 04 Jan 2016 14:02:07 GMT

Yes, that is what I want but I needed it to be a Calculated Dimension and not an Expression.

Because after that I need to check "Suppress When Value Is Null" and that option it is only available in the Dimensions tab and not in the Expressions.

Maybe you have other solution for me. Because what I want from start is:

Delete all the rows for this object where the value of Invoice.InternalDoc is NULL.

Re: Convert Null Values into String or Int

sunny_talwar — Mon, 04 Jan 2016 14:55:10 GMT

Check the attached

Re: Convert Null Values into String or Int

Oleg_Troyansky — Mon, 04 Jan 2016 15:38:43 GMT

I may be late to the party here, but let me offer an explanation and a solution... It's a known fact that NULL values cannot be selected - not manually and not in Set Analysis. However, using Set Analysis with Advanced Search, you can select another field's values that are associated with NULL values in the field in question.

For example, if you wanted to summarize all amounts for missing SupplierID values, you couldn't select NULL values in this field directly. However, you could apply a filter to another field - for example OrderNumber, and request those orders that are associated with NULL (or missing) Suppliers:

sum({<OrderNumber={"=len(trim(SupplierID))=0"}>} Amount)

From the performance standpoint, a solution that's implemented in an Expression is always better than a solution that's implemented in a Calculated Dimension, so I'd highly recommend to try Expressions first.

cheers,

Oleg Troyansky

Take your Qlik Skills to the next level at the Masters Summit for Qlik - now with new and redesigned materials!

Re: Convert Null Values into String or Int

Mon, 04 Jan 2016 16:05:23 GMT

Well, in fact in my sample qvw I do that and works like a charm.

But in the app that does not work. Must be some problem with the data

Thanks Very Much

Re: Convert Null Values into String or Int

Anonymous — Mon, 04 Jan 2016 16:08:30 GMT

Would it be this?