5 Replies Latest reply: Sep 26, 2014 3:29 PM by Sheetal Panchal RSS

    Display only rows that satisfies ALL conditions applied in Expressions

    Sheetal Panchal

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

            • Re: Re: Re: Display only rows that satisfies ALL conditions applied in Expressions
              Sheetal Panchal

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

          • Re: Display only rows that satisfies ALL conditions applied in Expressions
            Jyothish KC

            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