Restrict view in Straight Table

[#1]

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

[spsrk_84]

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

[spsrk_84]

Hi,

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

Regards,

Ajay

[#4]

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

[#5]

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

[#6]

Use Calculated Dimensions then 🙂

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

[#7]

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

[#8]

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).

[#9]

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

[#10]

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