Display only rows that satisfies ALL conditions applied in Expressions

[#1]

I have attached the sample file and output screenshot. I want to display only those rows that satisfies both the conditions (AND) instead of OR. i.e as per the expression applied, only one row satisfying both the condition should be displayed. Is this possible ?

Also, this is just a sample but in real scenario, I have about 10 such numeric columns that has lower and upper limits coming through variables whose values are entered by users

Data:

Sample_Data:

Load * Inline [

Company, ID, AccountType, No_Of_Emp, Revenue

A, 1, AAA, 28,300000

B, 1, BBB, 70,500000

C, 1, CCC, 80,450000

D,2, AAA, 200,2000000

E,2, BBB, 100,5000000

F,2, CCC, 70,750000

G,3, AAA, 300,6000000

H,4, BBB, 400,4500000

I,5, AAA, 100,2000000

J,6, CCC, 200,1500000

K,7, CCC, 250,2000000

L,8, AAA, 300,3000000

M,9, BBB, 100,400000

N,10,BBB, 105,900000

];

Dimension:

1. Company

2. ID

Expressions:

1. = Sum(Aggr(If(sum(No_Of_Emp)>200 AND SUM(No_Of_Emp) < 500, Sum(No_Of_Emp)), Company,ID))

2. = Sum(Aggr(If(sum(Revenue)>200000 and sum(Revenue) < 3000000, Sum(Revenue)), Company,ID))

[anbu1984]

Yes. Check this qvw

[anbu1984]

Check this

[jyothish8807]

Hi Sheetal,

Try this:

Expressions:

1. = Sum(Aggr(If((No_Of_Emp)>200 AND (No_Of_Emp) < 500, Sum(No_Of_Emp)), Company,ID))

2. = Sum(Aggr(If((Revenue)>200000 and (Revenue) < 3000000, Sum(Revenue)), Company,ID))

Regards

KC

[jyothish8807]

I guess you can remove SUM in the beginning also.Try both.

1. = Aggr(If((No_Of_Emp)>200 AND (No_Of_Emp) < 500, Sum(No_Of_Emp)), Company,ID)

2. = Aggr(If((Revenue)>200000 and (Revenue) < 3000000, Sum(Revenue)), Company,ID)

Regards

KC

[#6]

Thanks Anbu. These were very helpful and works with the above sample data and expressions. However, if I apply these to my another sample that has optional upper and lower limits applied to both columns and the limits controlled by variables whose values are entered by user input box, it suppresses all rows with 0 values in any of the column even when the limits are not selected which should not be the case.

I have attached the sample here. The optional lower and upper limits are working for both columns. If I apply your solution, the AND condition works but it suppresses all rows with 0 values in any of the column even when the limits are selected or not selected

If you cannot open the file, the data, dimension and expressions used in this sample are (vEmpLower, vEmpUpper , vRevLower  and vRevUpper  are variables whose values are entered through input box)

Data:

Sample_Data:

Load * Inline [

Company, ID, AccountType, No_Of_Emp, Revenue

A, 1, AAA, 28,300000

B, 1, BBB, 70,500000

C, 1, CCC, 80,450000

D,2, AAA, 200,2000000

E,2, BBB, 100,5000000

F,11, CCC, 70,750000

F,3, DDD, 0,750000

G,3, AAA, 300,6000000

G,12, BBB, 300,0

H,4, BBB, 400,4500000

I,5, AAA, 100,2000000

J,6, CCC, 200,1500000

K,7, CCC, 250,2000000

K,14, CCC, 0,2000000

L,8, AAA, 300,3000000

M,9, BBB, 100,400000

N,10,BBB, 105,900000

];

Dimension:

1. Company

2. ID

Expressions: - Working for optional limits but not AND condition

1.

if(vEmpLower <> '' AND vEmpUpper <> '', Sum(Aggr(If( sum(No_Of_Emp) >(vEmpLower) and sum(No_Of_Emp) < (vEmpUpper), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower <> '' AND vEmpUpper = '',Sum(Aggr(If( sum(No_Of_Emp) >(vEmpLower), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower = '' AND vEmpUpper <> '',Sum(Aggr(If(sum(No_Of_Emp)< (vEmpUpper), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower = '' AND vEmpUpper = '',sum(No_Of_Emp)))))

2.

if(vRevLower <> '' AND vRevUpper <> '', Sum(Aggr(If( sum(Revenue) >(vRevLower) and sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower <> '' AND vRevUpper = '',Sum(Aggr(If( sum(Revenue) >(vRevLower), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper <> '',Sum(Aggr(If(sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper = '',sum(Revenue)))))

Expressions modified to: - Working for AND conditions and optional limits but it suppresses rows with 0 values when any limits are not selected

1.

IF(if(vRevLower <> '' AND vRevUpper <> '', Sum(Aggr(If( sum(Revenue) >(vRevLower) and sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower <> '' AND vRevUpper = '',Sum(Aggr(If( sum(Revenue) >(vRevLower), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper <> '',Sum(Aggr(If(sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper = '',sum(Revenue))))),if(vEmpLower <> '' AND vEmpUpper <> '', Sum(Aggr(If( sum(No_Of_Emp) >(vEmpLower) and sum(No_Of_Emp) < (vEmpUpper), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower <> '' AND vEmpUpper = '',Sum(Aggr(If( sum(No_Of_Emp) >(vEmpLower), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower = '' AND vEmpUpper <> '',Sum(Aggr(If(sum(No_Of_Emp)< (vEmpUpper), sum(No_Of_Emp)), Company,ID)),

if(vEmpLower = '' AND vEmpUpper = '',sum(No_Of_Emp))))))

2.

IF(sum_No_Of_Emp,if(vRevLower <> '' AND vRevUpper <> '', Sum(Aggr(If( sum(Revenue) >(vRevLower) and sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower <> '' AND vRevUpper = '',Sum(Aggr(If( sum(Revenue) >(vRevLower), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper <> '',Sum(Aggr(If(sum(Revenue) < (vRevUpper), sum(Revenue)), Company,ID)),

if(vRevLower = '' AND vRevUpper = '',sum(Revenue))))))