topic Restrict view in Straight Table in QlikView

Restrict view in Straight Table

Mon, 22 Mar 2010 12:32:17 GMT

Morning All,

I have created a rec in a straight table. However, I only want to see rows where I have values (ie non null and non zero) for both my data sources. eg in the following:

ID	BalanceA	BalanceB	Diff
1	101	100	1
2	0	25	-25
3	51	0	51
4	142	122	20

I only want to see rows 1 and 4.

Can I do this in a Straight Table?

TIA

Tony

Restrict view in Straight Table

spsrk_84 — Mon, 22 Mar 2010 13:25:44 GMT

Hi,

Yes it is possible .please find the attached files and in the file i have created one more dimesion called as @@ID and applied the logic u said .One more thing when u take the dimesion @@ID in straight table dont forget to check/enable the option i.e "Supress When Value IS NuLL " in the Dimesion tab of the Straight table window.

Regards,

ajay

Restrict view in Straight Table

spsrk_84 — Mon, 22 Mar 2010 13:27:48 GMT

Hi,

I forgot to upload the Qvw file ,please find the attached....

Regards,

Ajay

Restrict view in Straight Table

Mon, 22 Mar 2010 13:40:46 GMT

In short: you need to have NULL values instead of 0 values

Restrict view in Straight Table

Mon, 22 Mar 2010 15:14:38 GMT

Thanks for all the help guys, but there's a few things I forgot to mention....

Firstly, I'm using Personal Edition, so unfortunately it seems I can't open anyone else's files. But I get the idea from what you've written that I need to introduce a NullFlag sort of field which is Null when either of my sources is zero.

However.... my BalanceA and BalanceB are not dimensions, but are expressions and are actually sums, ie Sum(BalanceA) and Sum(BalanceB).

I've been experimenting with the NullFlag field and it seems I can create it as an expression, which works great, but then I can't filter out records when an expression is null. Is that correct?

Alternately if I try to create the sums of BalanceA and BalanceB as calculated dimensions I just get the old "Error in calculated dimension" message.

Any ideas?

Thanks,

Tony

Restrict view in Straight Table

Mon, 22 Mar 2010 15:24:29 GMT

Use Calculated Dimensions then 🙂

When you go to the Dimensions tab, you will see that you can also add Calculated Dimensions (button right-middle).

Restrict view in Straight Table

Mon, 22 Mar 2010 16:15:02 GMT

When I try calculated dimensions I get "//Error in calculated dimension". Any ideas why that might be? Could it be because I have Nulls in the source data? I don't get it because sum(SourceA) works as an expression but not as a calculated dimension.

Tony

Restrict view in Straight Table

Mon, 22 Mar 2010 18:47:04 GMT

Hmm, expressions like SUM and COUNT don't work like that in a calculated dimension. It's really meant for stuff like adding text or if statements, etc.

Samples:


='ID: '&ID/2
=if(ID<10000,ID+10000,ID)

So make the Sum(SourceA) into a dimension, it must be loaded as a dimension (calculation while loading and putting the result in a field).

Restrict view in Straight Table

Tue, 23 Mar 2010 11:01:52 GMT

OK, thanks Mark. I'm wondering if I should try doing this in a Pivot Table instead. There must be some way to exclude zero values from a pivot table.... surely?

Tony

Restrict view in Straight Table

Tue, 23 Mar 2010 11:49:48 GMT

If you write

if(BalanceA<> 0,BalanceA)

and

if(BalanceB<> 0,BalanceB)

in calculatet dimension

and

mark Supress when value is NULL it will work.

Anders

Restrict view in Straight Table

Tue, 23 Mar 2010 12:27:32 GMT

I missed that BalanceA & BalanceB was expression.

You have to do like this

if(BalanceA<> 0 and BalanceB<> 0,ID)

and

mark Supress when value is NULL.

then you get this

Anders

=if(BalanceA<> 0 and BalanceB<> 0,ID)	sum(BalanceA)	sum(BalanceB)	sum(BalanceA) - sum(BalanceB)
	243	222	21
1	101	100	1
4	142	122	20

Restrict view in Straight Table

Tue, 23 Mar 2010 13:15:11 GMT

Thanks Ansch, that's looking good.

BTW do you know how to re-name a calculated dimension?

cheers,

Tony

Restrict view in Straight Table

Tue, 23 Mar 2010 13:23:13 GMT

Hi Tony

In the field just over the advanced-buttom you can re-name the field.

Anders

Restrict view in Straight Table

Tue, 23 Mar 2010 13:29:27 GMT

Got it. Thanks Anders.