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

[adilio_silva]

A more time-efficient approach:

Trace ========== Starting the distinct count ==========;

FOR i = 0 to NoOfTables()-1

    LET vTable = Tablename($(i));    

    LET vStringFields = 'LOAD ''$(vTable)'' as Table ' & Chr(10) & Chr(13);

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

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

        LET vStringFields = '$(vStringFields)' 
            & Chr(10) 
            & Chr(13) 
            & ', Count(Distinct [$(vField)]) as [$(vField)] ';              

    NEXT

    LET vStringFields = '$(vStringFields)' 
        & Chr(10) 
        & Chr(13) 
        & 'RESIDENT $(vTable)';

    // TRACE vStringFields = $(vStringFields);

    Distinct_Table:
    CrossTable('Column', 'Distinct_Values')
    $(vStringFields);

NEXT

TMP_Low_Cardinality:
LOAD 
    Column as t_Column
,   Table  as t_Table    
RESIDENT Distinct_Table
WHERE Distinct_Values = 1;

TMP_Concat_Fields:
LOAD
    Concat(t_Column, ']' & Chr(10) & Chr(13) & ',   [') as Columns_CONCAT
RESIDENT TMP_Low_Cardinality;

LET vLowCardinalityColumns = 'DROP FIELDS ' & Chr(10) & Chr(13) & '    [' & PEEK('Columns_CONCAT') & ']';

TRACE =========
Fields with Low Cardinality (1 distinct value):
$(vLowCardinalityColumns)
=========;

DROP TABLES TMP_Concat_Fields, TMP_Low_Cardinality;