topic Distinct value count of every column in a given table. in QlikView

Distinct value count of every column in a given table.

Anonymous — Wed, 28 Sep 2011 14:48:30 GMT

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

Re: Distinct value count of every column in a given table.

pernillet — Thu, 07 Feb 2013 09:47:20 GMT

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

Re: Distinct value count of every column in a given table.

Anonymous — Thu, 07 Feb 2013 10:46:46 GMT

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

Re: Distinct value count of every column in a given table.

pernillet — Thu, 07 Feb 2013 11:07:08 GMT

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.

Re: Distinct value count of every column in a given table.

Anonymous — Thu, 07 Feb 2013 11:25:16 GMT

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];

Re: Distinct value count of every column in a given table.

pernillet — Thu, 07 Feb 2013 11:49:10 GMT

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=?

Re: Distinct value count of every column in a given table.

pernillet — Thu, 07 Feb 2013 12:07:40 GMT

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;)

Re: Distinct value count of every column in a given table.

campbellr — Thu, 31 Jul 2014 23:32:46 GMT

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