topic Re: Row Suppression in QlikView

Row Suppression

Wed, 25 Nov 2020 16:16:04 GMT

I have created a straight table with two dimensions and 5 expressions

The dimensions are the customer name and code. The expressions work sales value etc.

My last expressions works out the difference between the exposure and the credit limit.

Some customers have obviously gone over their credit limit and show a negative value in this expression.

I need to exclude any rows that have a positive value in the final expression, basically include any customers that have gone over their credit limit. I know it is easy enough to exclude columns but not so easy with rows.

Any help would be greatly appreciated. Thanks in advance

Dave

Re: Row Suppression

sunny_talwar — Wed, 22 Mar 2017 15:33:06 GMT

May be use an if statement for all your expressions

If(Column(5) < 0, Expression1)

If(Column(5) < 0, Expression2)

If(Expression5 < 0, Expression5)

Re: Row Suppression

Thu, 23 Mar 2017 11:18:15 GMT

Hi Sunny,

Please excuse my ignorance. How can I reference columns in an expression.

Below is a subset of my data. I want to show all records that have a 1 in the 1or0 column

Customer	Leeway	1or0
Bunzl Retail & Healthcare Supplies	£60,000	0
Burtons Foods Ltd (108833)	£150,000	0
Big Bear Confectionery Ltd	£35,000	0
Globe Tech BV	£27,439	0
Branston Ltd	£100,000	0
Cott Beverages Ltd	-£312,372	1
Granton Medical Ltd	£2,307	0
Honey Monster Foods Ltd.	£60,000	0
Ice Fresh Seafoods Ltd	£20,000	0
Kettle Foods Ltd	£75,076	0
Y&S Recycling	£4	0
Moy Park Ltd (Belfast)	£11,036	0
My Protein	£177,999	0
Y&S Recycling Ltd	£1,000	0
Dunhills (Pontefract) PLC	£204,654	0
The Jordans & Ryvita Co Ltd	-£211,196	1
Plasto Sac Uk Ltd	£64,597	0
The Seafood Company Ltd.	£4	0
Clondalkin Flexible Pkg, Vaassen	£17,550	0
Web Dynamics International Ltd	£4	0
Riverside Medical Pkg Ltd	-£21,968	1
Tilda Ltd	-£61,186	1
Tata Steel UK Ltd	-£9,847	1

Re: Row Suppression

Anil_Babu_Samineni — Thu, 23 Mar 2017 16:03:04 GMT

May be use

Sum({<1or0 = {'1'}>}Leeway)

Re: Row Suppression

Thu, 23 Mar 2017 16:21:17 GMT

Thanks but that doesn't seem to work. I get another column just populated with 0

Re: Row Suppression

sunny_talwar — Thu, 23 Mar 2017 16:24:41 GMT

Assuming Leeway is an expression... try this and assuming 1or0 will be 1 or 0, you can try this

Leeway expression * [1or0]

where [1or0] is the expression label and replace Leeway expression with its actual expression you were using today. The idea is to make Leeway expression = 0 when 1or0 is 0

Re: Row Suppression

Thu, 23 Mar 2017 16:27:36 GMT

Hi, Dave.

Try to use condition in calculation dimension.

if([1or0] = 1, Customer).

Then, press "Suppress When Value Is Null".

If you don't have precalculated 1or0, you can calculate it in dimension.

if(aggr("Here must be expression" , Customer) = 1, Customer).

Re: Row Suppression

Thu, 23 Mar 2017 16:51:53 GMT

Hi Sunny,

Leeway is an expression so done the multiplication as suggested and I know have 0 or the correct figures. How can I suppress the 0s

Re: Row Suppression

sunny_talwar — Thu, 23 Mar 2017 17:17:08 GMT

May be check 'Suppress Zero Values' on the presentation tab

Re: Row Suppression

Fri, 24 Mar 2017 09:29:10 GMT

tried that and still all 0s appear

Re: Row Suppression

sunny_talwar — Fri, 24 Mar 2017 09:29:57 GMT

Would you be able to share your app with us?

Re: Row Suppression

Fri, 24 Mar 2017 10:16:54 GMT

how do I do that?

Re: Row Suppression

sunny_talwar — Fri, 24 Mar 2017 10:19:03 GMT

Check these links

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: Row Suppression

Fri, 24 Mar 2017 10:44:41 GMT

company policy won't let me send it even if we use the scrambler...

Re: Row Suppression

Fri, 24 Mar 2017 10:47:41 GMT

Hi!

Have you tried with dimension (see below)

Re: Row Suppression

sunny_talwar — Fri, 24 Mar 2017 11:16:19 GMT

Not sure what else to do... May be provide some dummy data in its raw form and explain what you want to see?

Re: Row Suppression

vinieme12 — Fri, 24 Mar 2017 12:34:47 GMT

if you only want to show negative values derived from your expression then do the below

if you want to show positive values just invert to ">" greater sign.

1) Expression

=IF( YourExpression < 0 , YourExpression)

2) On the Presentation tab check Suppress missing values

Re: Row Suppression

Fri, 24 Mar 2017 12:57:57 GMT

I can't get it to work. I don't think I can reference an expression in a dimension

Re: Row Suppression

Fri, 24 Mar 2017 13:00:33 GMT

unfortunately this doesn't seem to work

my expression is =if([Leeway]<0,[Leeway])

I have checked the Suppress Zero-Values and Suppress Missing and all the 0s still apppear

Re: Row Suppression

sunny_talwar — Fri, 24 Mar 2017 13:02:32 GMT

Leeway truely zero? Or is it something like 0.0000001 (a number greater than 0, but smaller than 1)? May be try this

=if(Round([Leeway]) < 0, [Leeway])

=if([Leeway] < 1, [Leeway])