Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Convert Null Values into String or Int

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

1 Solution

Accepted Solutions

Re: Convert Null Values into String or Int

Check the attached

10 Replies

Re: Convert Null Values into String or Int

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

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

Not applicable

Re: Convert Null Values into String or Int

Hello,

thanks for your reply.

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

Re: Convert Null Values into String or Int

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

Not applicable

Re: Convert Null Values into String or Int

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

Re: Convert Null Values into String or Int

Is this what you wanted?

Capture.PNG

Not applicable

Re: Convert Null Values into String or Int

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

Check the attached

MVP
MVP

Re: Convert Null Values into String or Int

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!

Not applicable

Re: Convert Null Values into String or Int

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

Community Browser