topic Re: Possible Selection? in QlikView

Possible Selection?

tamilarasu — Thu, 21 May 2015 10:04:24 GMT

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.

Re: Possible Selection?

awhitfield — Thu, 21 May 2015 10:10:40 GMT

Hi Tamil,

to start with, your employee IDs don't match between the tables, they are prefixed by Q in the QC table

Andy

Re: Possible Selection?

tamilarasu — Thu, 21 May 2015 10:21:05 GMT

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.

Re: Possible Selection?

tamilarasu — Fri, 22 May 2015 01:41:48 GMT

Any Idea?

Re: Possible Selection?

danieloberbilli — Fri, 22 May 2015 02:22:19 GMT

like that? please find .qvw attached

Re: Possible Selection?

tamilarasu — Fri, 22 May 2015 02:53:25 GMT

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

Re: Possible Selection?

ToniKautto — Fri, 22 May 2015 03:01:40 GMT

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.

Re: Possible Selection?

ToniKautto — Fri, 22 May 2015 03:05:55 GMT

Is this what you expect, or what is the expected result?

Re: Possible Selection?

danieloberbilli — Fri, 22 May 2015 03:06:11 GMT

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

Re: Possible Selection?

tamilarasu — Fri, 22 May 2015 03:50:25 GMT

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.?

Re: Possible Selection?

tamilarasu — Fri, 22 May 2015 04:03:08 GMT

Hi Daniel,

As I said already, Emp ID key in Qc table is belongs to the QC persons. We can take the QC count from QC table and match the worked by name with the Emp Name from productivity table. Is there any possibility?

I guess, I haven't clearly explained my doubt.

Re: Possible Selection?

ToniKautto — Fri, 22 May 2015 04:12:03 GMT

Here is my example for the screenshot. Notice that I have linked the table over EmpName and WorkedBy. I think you should spend time on the data modelling to make sure you organize the data to represent how it relates.

As you can see by a "correct" data model you also get a lot easier expressions.

Re: Possible Selection?

tamilarasu — Fri, 22 May 2015 04:20:19 GMT

Thanks a lot Toni. But sorry to say, I can not edit data model as it was already built. The table is linked with date key. Please see above attachment names as "QComm". Could you provide me front end solution.!!

Re: Possible Selection?

danieloberbilli — Fri, 22 May 2015 04:28:45 GMT

I think it is as I said - it depends on your dimension and what field you count:

in this example I show the dimension of QC and count a field from QC whereas the Productivity-reference is in the p() function

Re: Possible Selection?

tamilarasu — Fri, 22 May 2015 04:56:25 GMT

Thank you for your time Daniel. My dimension is Productivity.EmpName (Its already defined) and I tried the below expression. But the count is wrong.

=count({<[Productivity.EmpName]=P([QC.Worked by])>} [QC.Worked by])

Re: Possible Selection?

danieloberbilli — Fri, 22 May 2015 05:36:04 GMT

Then I dont think that this is possible -if you are not 'allowed' to change the object, try add another table next to it / or show with button on/off

Re: Possible Selection?

Fri, 22 May 2015 05:41:59 GMT

Hi Tamil,

try this you will get little idea.

=count(DISTINCT{<[Productivity.EmpName]=P([QC.Worked by])>} EmpID)

Re: Possible Selection?

ToniKautto — Fri, 22 May 2015 05:51:51 GMT

Keep in mind that if the EmpID in the two tables do not refer to the same person, your link will simply not reflect the truth. In this small example there is no single value that links, so the link it self is misleading.

Re: Possible Selection?

ToniKautto — Fri, 22 May 2015 05:53:51 GMT

Agree with Daniel. Either your object has unsuitable setup for your purposes, or your data model does not match the business reality. I do not see that we can help you resolve this since the data does not match your expectations.

Re: Possible Selection?

tamilarasu — Fri, 22 May 2015 06:10:43 GMT

Toni and Daniel: In that is the case, we can skip the above issue. I just want to know whether the below can be achieved through expression.

Sorry to bother you again on this.