6 Replies Latest reply: Feb 2, 2018 12:14 PM by omar bensalem RSS

    COUNT VALUES IN DIFFERENT COLUMNS

    Diana Socha

      Hi people

       

      In this case I need to know how many times appears the same value in different columns, like this

       

       

      COL1     COL2     COL3     COL4

           A           B            A          E     

           B          C            A          E

           C          B            B          A        

           D          A            B           B  

           E          E            B          E

       

       

      'E' Value appear five times in different columns, I'm working with Qlik Sense... i can't do this using expressions like " Value = 'E' "

       

       

      Please Help me!!!

        • Re: COUNT VALUES IN DIFFERENT COLUMNS
          Paul Scotchford

          Just a thought, if you are doing this at load time you could potentially concatenate each set of columns to a single column in each row and use SubStringCount(text, sub_string) to perform your count.


          Caveat : This is a quick top of my head thought, may require clever coding and depending on your data set volume could be resource hungry.

          • Re: COUNT VALUES IN DIFFERENT COLUMNS
            omar bensalem

            I have one idea :

             

            in the script, create a new field as follow:

             

            table:

            load * Inline [

            COL1,    COL2,    COL3 ,    COL4

                A ,          B    ,        A      ,    E 

                B  ,        C    ,        A    ,      E

                C  ,      B    ,        B  ,      A     

                D    ,      A    ,      B  ,        B

                E    ,    E,            B  ,      E

            ];

             

             

            load COL1 as Selection Resident table;


            Now, in the presentation let's create a new variable called: vSelect = Selection

            Capture.PNG

             

            Now, create a KPI object and as its measure, put :

            sum(total aggr(count( {<COL1={"$(vSelect)"}> } COL1) +

            count( {<COL2={"$(vSelect)"}> } COL2)+

            count( {<COL3={"$(vSelect)"}> } COL3)+

            count( {<COL4={"$(vSelect)"}> } COL4),COL1))

             

            label it as follow: ='Count of '&Selection

            Under add_on, put a condition:

            count(distinct Selection)=1

            Capture.PNG

             

            Now, add u Selection field as a filter object

             

            Result:

            Capture.PNG

            Capture.PNG

            Capture.PNG

             

            etc...

             

            Hope that was helpful !

             

            Omar BEN SALEM.

            • Re: COUNT VALUES IN DIFFERENT COLUMNS
              omar bensalem

              Another way to do :

               

              table:

              load RowNo() as row,* Inline [

              COL1,    COL2,    COL3 ,    COL4

               

                  A ,          B    ,        A      ,    E

                  B  ,        C    ,        A    ,      E

                  C  ,      B    ,        B  ,      A  

                  D    ,      A    ,      B  ,        B

                  E    ,    E,            B  ,      E

              ];

               

               

              Crosstable (Cols, Values) LOAD * resident table;


              Then in the presentation; you create a table:

              Dimension: Values

              Measure: count(Values)

               

              result:

              Capture.PNG

              • Re: COUNT VALUES IN DIFFERENT COLUMNS
                omar bensalem

                Havnt any of the provided solutions solved ur pblm? If yes close the thread plz by markinh the correct answer as correct or tell me if ure still seeki g for another solurion

                Thks