
Re: Display only rows that satisfies ALL conditions applied in Expressions
anbu cheliyan Sep 25, 2014 2:03 PM (in response to Sheetal Panchal )Yes. Check this qvw

limit_rows_an.qvw 138.8 K

Re: Re: Display only rows that satisfies ALL conditions applied in Expressions
anbu cheliyan Sep 25, 2014 2:11 PM (in response to anbu cheliyan)Check this

limit_rows_an_1.qvw 141.5 K

Re: Re: Re: Display only rows that satisfies ALL conditions applied in Expressions
Sheetal Panchal Sep 26, 2014 3:29 PM (in response to anbu cheliyan)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))))))

test_2.qvw 163.2 K




Re: Display only rows that satisfies ALL conditions applied in Expressions
Jyothish KC Sep 25, 2014 2:12 PM (in response to Sheetal Panchal )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

Re: Display only rows that satisfies ALL conditions applied in Expressions
Jyothish KC Sep 25, 2014 2:16 PM (in response to Jyothish KC)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
