topic Re: Issue using P() inside a Straight Table in QlikView in QlikView

Issue using P() inside a Straight Table in QlikView

joshabbott — Tue, 04 Sep 2018 19:54:18 GMT

Hello!

I'm having an issue that I can't seem to get past. I am uploading a sample document. It has a straight table with 1 dimension (Country). There are 2 other fields, an ID (represents an employee ID) and a ManagerID (represents the ID of the manager of the employee).

Here is the inline table I'm loading:

LOAD * INLINE [
ID, ManagerID, Country
1, , USA
2, 1, USA
3, 6, USA
4, 5, USA
5, 1, Canada
6, 1, Canada
];

Here are the expressions:

1 - Total Employees - count(ID)

2 - Total Managers that are also Employees - Count({<ManagerID=P(ID)>}DISTINCT ManagerID)

The issue above is 2. I have 4 total employees for the USA, but for the manager count, it is showing 3 even though only 1 of those managers is from the USA. I want this to show 1, and thought this would work with the 'P()' function. However, the P() function doesn't seem to limit the data to the dimensional values in the straight table, instead it is counting everything that is selected in total (so values in Canada are being calculated in the 'USA' row. Could someone help me on a possible solution to this? Obviously, the attached is small, I'm dealing with a much larger data set and many more countries than the 2 in the example.

Thank you!

Josh

Re: Issue using P() inside a Straight Table in QlikView

swuehl — Tue, 04 Sep 2018 20:17:59 GMT

Set Analysis is only evaluated once per chart, not per dimension line, so this won't work.

Maybe something like this:

INPUT:

LOAD *

INLINE [

ID, ManagerID, Country

1, , USA

2, 1, USA

3, 6, USA

4, 5, USA

5, 1, Canada

6, 1, Canada

];

LEFT JOIN

LOAD ID as ManagerID, Country as ManagerCountry

Resident INPUT;

Country	=count(ID)	=count(if(Country = ManagerCountry, ID))
	6	1
Canada	2	0
USA	4	1

You can flag the condition in the script and then use set analysis for better performance.

Re: Issue using P() inside a Straight Table in QlikView

joshabbott — Wed, 05 Sep 2018 11:36:37 GMT

Thank you for your reply, I guess my biggest question is why 'P()' in a straight table isn't using the possible values of the row it is on, instead it is using the 'possible values' for everything being shown. That doesn't seem right, so possible in every row is exactly the same.

Re: Issue using P() inside a Straight Table in QlikView

trdandamudi — Wed, 05 Sep 2018 21:04:34 GMT

Please see the attached file and hope this helps:

Any of the following expression will work:

max(Aggr(Count({$<ManagerID = P({<ManagerID={"=ID"}>})>} Distinct ManagerID), Country,ManagerID))

max(Aggr(Count({$<ManagerID = P({<ManagerID={"=ID"}>})>} Distinct ManagerID), ManagerID,Country))

Aggr(Count({$<ManagerID = P({<ManagerID={"=ID"}>})>} Distinct ManagerID), ManagerID,Country)

Aggr(Count({$<ManagerID = P({<ManagerID={"=ID"}>})>} ManagerID), ManagerID,Country)

Re: Issue using P() inside a Straight Table in QlikView

swuehl — Wed, 05 Sep 2018 21:32:48 GMT

Josh Abbott wrote:

Thank you for your reply, I guess my biggest question is why 'P()' in a straight table isn't using the possible values of the row it is on, instead it is using the 'possible values' for everything being shown. That doesn't seem right, so possible in every row is exactly the same.

p() is a function that can only be used in a set expression. And as I said this set expression is not evaluated per dimension, but once for the whole chart. You can see the set expression as a way to create a field selection context per chart expression.

The p() function is not much different from using the context menu on a list box 'Select possible'.