COUNT VALUES IN DIFFERENT COLUMNS

[dmpilars]

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

[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.

[OmarBenSalem]

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

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

Now, add u Selection field as a filter object

Result:

etc...

Hope that was helpful !

Omar BEN SALEM.

[dmpilars]

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

[OmarBenSalem]

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

[OmarBenSalem]

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:

[OmarBenSalem]

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