Convert Null Values into String or Int

[#1]

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

[sunny_talwar]

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

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

[#3]

Hello,

thanks for your reply.

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

[sunny_talwar]

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

[#5]

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

[sunny_talwar]

Is this what you wanted?

[#7]

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.

[sunny_talwar · Accepted solution]

Check the attached

[Oleg_Troyansky]

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!

[#10]

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