Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

dmpilars
New Contributor II

COUNT VALUES IN DIFFERENT COLUMNS

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

6 Replies
paul_scotchford
Valued Contributor

Re: COUNT VALUES IN DIFFERENT COLUMNS

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.

OmarBenSalem
Esteemed Contributor

Re: COUNT VALUES IN DIFFERENT COLUMNS

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.

dmpilars
New Contributor II

Re: COUNT VALUES IN DIFFERENT COLUMNS

Thanks! can u explain to me the expression: COUNT (distinct selection) = 1

OmarBenSalem
Esteemed Contributor

Re: COUNT VALUES IN DIFFERENT COLUMNS

It's that the KPI object won't appear unless u select only one value from the selection field == count(distinct selection)=1.

OmarBenSalem
Esteemed Contributor

Re: COUNT VALUES IN DIFFERENT COLUMNS

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

OmarBenSalem
Esteemed Contributor

Re: COUNT VALUES IN DIFFERENT COLUMNS

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

Community Browser