topic Re: Excluding dimensons that are not equal to null in QlikView

Excluding dimensons that are not equal to null

Tue, 11 Aug 2015 13:46:56 GMT

I have a dimension where some of the records are null and some contain a 4 digit code. I only want to include the null records but when I try to create a calculated dimension I just get blank records in the value fields

I've attached my QVD and its the table that is showing trading partner is where I'm looking to only include the null value records

Re: Excluding dimensons that are not equal to null

sunny_talwar — Tue, 11 Aug 2015 13:55:54 GMT

May be try this:

=If(Len(Trim(Field)) = 0, 'Null') and check 'Suppress When Value Is Null' option on the Dimension tab of chart properties.

HTH

Best,

Sunny

Re: Excluding dimensons that are not equal to null

Tue, 11 Aug 2015 13:58:04 GMT

Thanks

But I'm trying to include the NULL values not exclude them

Re: Excluding dimensons that are not equal to null

sunny_talwar — Tue, 11 Aug 2015 13:59:18 GMT

That's what I am intending to do also. Give this a try and see if it gives you the result you are expecting.

Re: Excluding dimensons that are not equal to null

lironbaram — Tue, 11 Aug 2015 14:00:31 GMT

you can try the example in the attach model

Re: Excluding dimensons that are not equal to null

Tue, 11 Aug 2015 14:04:22 GMT

I added that to a calculated dimension and changed all the values to read Null but left the records there

Re: Excluding dimensons that are not equal to null

Tue, 11 Aug 2015 14:06:08 GMT

Sorry I can't see a model attached,am I missing something

Re: Excluding dimensons that are not equal to null

lironbaram — Tue, 11 Aug 2015 14:18:33 GMT

i changed your calculated dimension to

=if(isnull(TradingPartner),'null') and checked the suppress null values

Re: Excluding dimensons that are not equal to null

Gabriel — Tue, 11 Aug 2015 14:26:54 GMT

Hi,

Why not create a flag in the script with

IF(ISNULL(TradingPartner),1,0) AS Flag_TradingPartner

And in the dimension use this new fields as dimension with Set Analysis of where Flag_TradingPartner ={1}

Re: Excluding dimensons that are not equal to null

Tue, 11 Aug 2015 14:30:45 GMT

Thanks

I get the first part and I can add that to my script, I'm not sure about the set analysis. Would I do this as a calculated dimension or include it in myu expressions

Re: Excluding dimensons that are not equal to null

Gabriel — Tue, 11 Aug 2015 14:41:26 GMT

Hi,

That's just an idea of what the Set Analysis should look like.

Try this

TEXT({<Flag_TradingPartner ={1}>} Flag_TradingPartner)

Re: Excluding dimensons that are not equal to null

Tue, 11 Aug 2015 14:43:53 GMT

Do I create this as a dimension ?

Re: Excluding dimensons that are not equal to null

Gabriel — Tue, 11 Aug 2015 14:51:16 GMT

Yes