Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
Supposed I have a data model with a star schema. Field A is the common field that links more than one table together. How can I count the number of distinct value in field A in different tables?
Thanks
hi
=count(distinct fieldname)
Hi Ivy,
To count distinct value try like below:
Count(Distinct filedA) this will give the result.
If you want Distinct count for each table then need to make changes to your script by adding the script below.
Table1:
Load autonumber(filedA) as Identifier_Table1,
fieldA,
*
from ....
Table2:
Load autonumber(filedA) as Identifier_Table2,
fieldA,
*
from ....
Now for Table1 > Count( Distinct Identifier_Table1)
and for table2> Count( Distinct Identifier_Table2)
Hope this helps you
hi
suppose u have 3 table. and field A is common in all three table.
then in first table : A as A_tab1
then in second table : A as A_tab2
then in third table : A as A_tab3
now
count(distnict A_tab1)
count(distnict A_tab2)
count(distnict A_tab3)
tab1:
load
A,
A as newfield
.....
tab2:
load
A,
A as field
.....
count newfield or field
In effect, Avinash tells you to make a copy (or in his case using autonumber) of the filedA key in each table, so you can count the distinct values of the key, but also of each copied key.
thanks a lot for your help. I have another question. Supposed I have 2 fields, and I want to merge them together so that I can count the number of distinct values in the across the 2 fields. Is there any way to achieve this?
in script
field1 & '-' & field2 as newfield
in chart
count(distinct field1 & '-' & field2)
Hi
Might be go to table viewer where populate your data relation diagram , there you just put a cursor on that Primary key field. it 'll show you