Distinct Row Counts for All Columns

[yongtaufoo]

Hi, I would like to know if there is any way to compute the distinct count of rows for 50 columns in a table without manually adding 50 over measures and dimensions, or to edit the load editor.

For example, Table1 has 50 columns and more than 1 million rows of entries. It would be rather tedious to do count(distinct [col1]), ..., until count(distinct [col50]) and I would not be able to analyse this information in a chart.

I am thinking of creating a new table called Table2, with two columns, ColumnName and DistinctCount, which can store the number of distinct values for each column in Table1.

Please advice on what I can do, or if there is a better way to solve this problem. Thank you for your time and attention!

[sapkaleshivani]

Hello,

Consider as sample dataset:

ABC :
Load * Inline
[col1,col2,col3
1,a,d
1,a,d
2,b,e
2,b,e
3,c,f
4,c,f];

For each a in 'col1','col2','col3'      //add all your columns here
Trace $(a);
DEF :
Load
'$(a)' as column_name,
COunt(DISTINCT $(a)) as count
Resident ABC group By $(a);
next a;

Go to front end drag a table and take dimension as column_name and measure as sum(count)

Hope this will help you out.

Thank you.

[yongtaufoo]

Hi,

Thank you very much for the reply. This method works very well.

I have found another method, which is to create a master dimension [$Field] and master measure FieldValueCount([$Field]). This method works without using the Data Load Editor.

Thank you for your attention. 

[shivanisapkale]

Hello,

For each a in 'col1','col2','col3'      //add all your columns here
Trace $(a);
DEF :
Load
'$(a)' as column_name,
COunt(DISTINCT $(a)) as count
Resident ABC group By $(a);
next a;

If you have found this code useful,mark it as solution and close this query .

Regards,

Shivani Sapkale

[tiagopost]

for i = 0 to NoOfTables()-1

    let vTable = tablename($(i));

    trace $(i) : $(vTable);

    FOR x = 1 to NoOfFields('$(vTable)')

        let vField =  FieldName($(x),'$(vTable)');

        trace $(x) : $(vField);

        dist:

        LOAD

            '$(vTable)'                 as tabela,

            '$(vField)'                 as campo,

            count(distinct [$(vField)]) as dist_val

        resident [$(vTable)];

    NEXT x

next i