Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
joshabbott
Creator III
Creator III

Issue using P() inside a Straight Table in QlikView

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

4 Replies
swuehl
MVP
MVP

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
Canada20
USA41

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

joshabbott
Creator III
Creator III
Author

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.

trdandamudi
Master II
Master II

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

OR

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

OR

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

OR

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

swuehl
MVP
MVP

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