Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have two tables and uploaded from two excel files and both linked with Emp Id key. The table names are Productivity and Qc. Now, I want to get the qc count for the employee whose name present in productivity. I have tried like below but unable to get the result.
count({<Productivity.EmpName=P(QC.WorkedBy={'$(Productivity.EmpName)'})>}EmpID)
Please see the attachment and provide the solution please.
Hi Tamil,
to start with, your employee IDs don't match between the tables, they are prefixed by Q in the QC table
Andy
Hi Andrew,
My mistake. Its just a sample file. The Id's are belongs to the persons who done quality check for the employees. Say for example, I have to show the Qc count for the employee name 'Abi' as 1. He got one qc count in Qc table. Like wise siva should get 2. I have changed the Emp id to date key. Hope you understand my problem.
Any Idea?
like that? please find .qvw attached
Hi Daniel,
I can see count mismatch in the table. Please see below.
In Qc table, Employee siv has two Qc counts and rest having only one Qc count. !
The sample file is not properly loading the data. In table QC the actual headers are loaded as the first data row, which is probably not what you want.
As best practice I think you should prefix your key fields, so that you distinguish them from the other fields. You should not use the key fields in your dimensions, expressions or calculations. A key field contains the values of both associated tables, so a aggregation might allocate more memory than intended and target unexpected data.
For example if you perform a Count() in the key field, what value are you expecting? Rows in TableA, rows in TableB, an intersection of the rows in both?
To resolve this logical problem, simply duplicate the key field in the table. And use the non-key field in aggregations.
As suggested previously you need to normalize your key values so that they actually match. In other words remove the leading Q in QC.EmpID.
If the attached is not what you expect, please clarify what the resulting table should look like.
Is this what you expect, or what is the expected result?
Sorry I cant follow your data - it seems that your EmpName has now become WorkedBy.
I think you just need to be aware of your straight (or pivot) table dimension and expression:
if you want to see the # of the QC table then use the employee field of the QC table as dimension ...in your example you are using the field from productivity
Hello Toni,
Yes. The above table is what I expected. I want to display the Qc count done for that particular employee. But there are no Emp id field for that employees in QC table (rather emp id for the Qc persons). The only way we can match the count by using the Worked by name field. Hope you get my point. Is it possible.?