Distinct value count of every column in a given ta... - Qlik Community

Anonymous · ‎2011-09-28

This wil retrieve the distinct value count of every column in the given table.

let tablename='Tablename';

Count:
load * inline [name,count];

for i=1 to NoOfFields('$(tablename)')

let vcolumnname=FieldName(i,'$(tablename)');

     concatenate (Count)
      load
        '$(vcolumnname)' as name,
        FieldValueCount('$(vcolumnname)') as count;
      load * inline [name,count

'',0];
next i;

------------------------ input -----------------------------

a b c d e f

a 1 1 1 1 1

b 1 4 2 2 1

c 2 3 1 3 1

d 1 1 2 4 1

e 2 3 3 5 1

f 2 1 4 6 1

g 1 4 5 1 1

----------------------- output---------------------------------

Column count

a 7

b 2

c 3

d 5

e 6

f 1

pernillet · ‎2013-02-07

aren't you missing drop table temp? And do you know, if I have a number of distinct values, how I can count how many values I have per distinct values?

Best Pernille

Anonymous · ‎2013-02-07

what do you think of this?

let tablename='tablename';

Count: load * inline [name,value,count];

for i=1 to NoOfFields('$(tablename)')
      let vcolumnname=FieldName(i,'$(tablename)');
      concatenate (Count)
      load
        '$(vcolumnname)' as name,
        $(vcolumnname) as value ,
        count(1) as count,
        1 as tmp
      resident $(tablename)
      group by $(vcolumnname);
next i;

left join (Count)
load
      name,
      count(tmp) as distinct
resident Count
group by name;

drop Field tmp;

--------------------------- input -------------------------------

a b c d e f

-----------

a 1 1 1 1 1
b 1 4 2 2 1
c 2 3 1 3 1
d 1 1 2 4 1
e 2 3 3 5 1
f 2 1 4 6 1
g 1 4 5 1 1

--------------------------- Result ------------------------------

Column distinct value count

a           7      a      1
a           7      b      1
a           7      c      1
a           7      d      1
a           7      e      1
a           7      f      1
a           7      g      1
b           2      1      4
b           2      2      3
c           3      1      3
c           3      3      2
c           3      4      2
d           5      1      2
d           5      2      2
d           5      3      1
d           5      4      1
d           5      5      1
e           6      1      2
e           6      2      1
e           6      3      1
e           6      4      1
e           6      5      1
e           6      6      1

pernillet · ‎2013-02-07

Great code, but the value counts the distict number of values. I need the number of values of each distinct value. so.....value z= 455 times in my table. value v=42 times. Now it is saying z= 37 ( numbers of x,y,z, h, ) v=37.

Anonymous · ‎2013-02-07

Try and use the following as input table.

tablename:

load * inline [a,b,c,d
a,1,1,1,1,1
b,1,4,2,2,1
c,2,3,1,3,1
d,1,1,2,4,1
e,2,3,3,5,1
f,2,1,4,6,1
g,1,4,5,1,1];

pernillet · ‎2013-02-07

in this case, I would like to look at name and get the following result:

a=7

b=2

c=3

d=5

new key=?

pernillet · ‎2013-02-07

I dont know a b c d, I have a tabel with

a, a,a, b,b,b,c,c,c,c,d,d,d,d,d,d,e,e,e,e

but the next day the table could be

a,a,a,a,h,h,h,b,b,e,e,g,g,g. So I'm trying to use the code example to make some kind of loop to get every indbut, and then yes, then I could count distinct, and then load an inline table, but I'm missing the mittlepart;)

campbellr · ‎2014-07-31

Hi Pernille

Do you need the count of occurrences of a value to show in your data? Could you not just use a pivot table to calculate how often each value occurs?

I may be missing something.

Ron

Distinct value count of every column in a given table.