topic Re: Need Help in QlikView

Need Help

paulwalker — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community,

This is my table...

load * Inline [

Cat, InRate, OutRate

A,200,200

A1,12,0

B,99,98

C,123,123

D,24,19

E,1200,100

];

If i have same Rates (InRate and OutRate), I want to Excluede...

I want to show only variance data.

Here my 3rd expression InRate-OutRate=0, I want exclue.

My output should be..

Cat	sum(InRate)	sum(OutRate)	Column(1)-Column(2)
A1	12	0	12
B	99	98	1
D	24	19	5
E	1200	100	1100

Thanks in Advance....

Re: Exclude 0 Values using expression

Fri, 22 Jul 2016 01:19:24 GMT

Hi,

Create a calculated dimension as follows

=aggr(if(sum(InRate)-Sum(OutRate) <>0, Cat), Cat)

and then tick Suppress when NULL

Re: Exclude 0 Values using expression

Fri, 22 Jul 2016 01:24:13 GMT

Please find attached.

Re: Exclude 0 Values using expression

maheshkuttappa — Fri, 22 Jul 2016 01:33:36 GMT

Load

InRate-OutRate as InOutrate

where InRate-OutRate >0;

load * Inline [

Cat, InRate, OutRate

A,200,200

A1,12,0

B,99,98

C,123,123

D,24,19

E,1200,100

];

Cat	sum(InRate)	sum(OutRate)	Sum(InOutrate)
A1	12	0	12
B	99	98	1
D	24	19	5
E	1200	100	1100

Re: Need Help

Fri, 22 Jul 2016 02:23:47 GMT

If you want to exclude those rows from loading to dashboard, may add following in the script:

Data:

NoConcatenate

load

Resident Temp

where InRate <> OutRate;

drop table Temp;

Otherwise, as shown above, it can be excluded in the object itself as well.

Re: Need Help

Fri, 22 Jul 2016 02:33:30 GMT

Indeed, this should be solved the problem.

Re: Need Help

paulwalker — Fri, 22 Jul 2016 03:53:49 GMT

Hi Ram,

i have more than 10 dimensions..

any another way ???

Re: Need Help

settu_periasamy — Fri, 22 Jul 2016 05:36:19 GMT

You can try the expression like

expr 1: if(sum(InRate)<>sum(OutRate),sum(InRate))

expr 2: if(sum(InRate)<>sum(OutRate),sum(OutRate))

expr 3: Column(1)-Column(2)

Dimension: Cat

sample attached:

Re: Need Help

susovan — Fri, 22 Jul 2016 05:59:24 GMT

Hi Paul,

Use this below expression as a calculative dimension,

Exp: Aggr(if((Sum(InRate-OutRate))=0,Null(),Cat),Cat)