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