7 Replies Latest reply: Dec 19, 2014 9:57 AM by Anand Chouhan RSS

    Count

      Hi,

      I have a col having YES and NO as data.I want to count no.of YES and NO

       

      if(col='YES',count(col) ) as col1;

       

      but it is showing error.

      Can anyone help me please.

        • Re: Count
          Serhan Celebi

          Hello,

           

          You can do this at front end, not in the script (in the script it is harder and inefficient).

           

          count({<col={'YES'}>} col)

           

          This technique is called Set Analysis. You can check in help.

           

          BR

           

          Serhan

          • Re: Count
            neetha P

            Hi,

             

            are you using group by in the script

            • Re: Count
              neetha P

              Hi,

              use serhan's solution in UI:

               

              count({<col={'YES'}>} col)

               

              or

               

              in script you can use:

              Table:
              Load * inline [Ref,Product,Value,col
              1,111,700,yes
              2,111,400,no
              1,115, 1000,yes
              2, 116,800,no
              3, 116, 600
              3, 141, 900
              4, 150, 200
              4, 181, 100
              5, 182, 400]
              ;

              NoConcatenate
              LOAD *,
              col as YesCal
              Resident Table
              where col = 'yes';
              concatenate
              LOAD *,
              col as NoCal
              Resident Table
              where col = 'no';

               

              in UI:

               

              =count(NoCal)

               

              Regards

              Neetha

              • Re: Count
                Anand Chouhan

                Hi,

                 

                1. You can use the SET analysis in the application like

                 

                     count({Col={'YES'}}  Col)  And count({Col={'NO'}}  Col)

                 

                2. In the load script also as you create another fields

                 

                Tab1:

                Load Col From Source;

                 

                Load Id,

                Count( If(Col='YES', Col )) as YesCount,

                Count( If(Col='NO', Col )) as NoCount

                Resident Tab1 Group BY Id;

                 

                Hope this helps

                Regards

                Anand

                  • Re: Count

                    Thanq Anand

                    Continuing this.. i have 3YesCounts for 3different tables withe 3residents .how to get sum of these 3YesCounts?

                      • Re: Count
                        Ankita Agarwal

                        Hi Ramya,

                         

                        You can concatenate those three resident tables and then sum the counts in another table using the resident. Also, if those three tables are different from each other then at the front end you can simply use

                         

                        =sum(YesCount1)+sum(YesCount2)+sum(YesCount3)

                        andsame with theNoCounts.

                         

                        Attached is the example of the same.

                         

                        Thanks and Regards,

                        Ankita

                        • Re: Count
                          Anand Chouhan

                          Hi,

                           

                          If you have any sample then please provide with few rows or you can simply concatenate them if fields names are same otherwise rename them

                           

                          Load YesCountField Resident 1;

                          Concatenate

                          Load YesCountField Resident 1;

                          Concatenate

                          Load YesCountField Resident 1;

                           

                          And in Front end do sum.

                           

                          Regards

                          Anand