Indirect Selection (P function etc.)

[#1]

Hi

I've two data elements in two different tables

Table_1.contact_id (this is one of the data elements in Table 1)

Table_2.contact_id (this is one of the data elements in Table 2)

The data model is structured in a way that these two tables are not linked using contact_id because of circular reference issues in the larger scheme of data model.

However, for certain computations i need to assign Table_2.contact_id based on the selection of Table_1.contact id.

i.e. The user on the front end is going to select Table_1.contact id, However, when that selection is made i need to make that same selection on Table_2.contact_id

I've tried using p function to do this. For example, writing the code Table_2.contact_id = p(Table_1.contact_id)

For some reason this didn't work.

Is there any other way to make this indirect selection?

Thanks

[swuehl]

So there don't seem to be any matching values for this field modifier assignment, right?

security_id_effective_date2 = p(security_id_effective_date)

[#3]

There is. Just haven't included it in the screen shot.

[#4]

Below is the screenshot with security_id_effective_date and security_id_effective_date_2

[swuehl]

I still don't see any potentially matching values.

Maybe this that's just because of the value selected or range of list box values shown, but unless you can find matching values for the p() assignments, I wouldn't expect the set expression to return something.

[vgutkovsky]

I'm getting at the same thing swuehl is. Is there, in fact, a matching value for your dates?

[#7]

Hi Swuehl/Vlad

There are matching records in both

security_id_effective_date2 = security_id_effective_date as well as

history_rating_contact_id = rating_contact_id

May be the picture below confirms it.

Thanks

[swuehl]

I still don't see matching values for effective dates.

One list ends (in your screenshot) at 301... the other list box starts at 476...?

[vgutkovsky]

Yeah, QlikView is just trying to compare text strings for your dates, which clearly do not match in your case. You might need to do some data transformation to create true matching values.

Vlad

[#10]

Apologies for repeating but let me explain the problem and what the code is doing step-by-step.

Goal: Narrow down the records to arrive at the correct total for benchmark_contrib_mktval_weight

Starting Point Observations:

                    count of rating_contact_id: 2144

                    count of history_rating_contact_id: 1171

                    count of security_id_effective_date: 1201

                    count of security_id_effective_date2: 4032

                    count of benchmark_contrib_mktval_weight:4032

                    sum of benchmark_contrib_mktval_weight: 800%

Part of Data Model:

Here is what's happening step by step

Step1: User selects rating_contact_id 917 (Note, this attribute is in core_rating_table)

                    Observations:

                    count of rating_contact_id: 16

                    count of history_rating_contact_id: 60

                    count of security_id_effective_date: 60

                    count of security_id_effective_date2: 4032

                    count of benchmark_contrib_mktval_weight:4032

                    sum of benchmark_contrib_mktval_weight: 800%

Step2: User selects parent_clone_type Parent

                    Observations:

                    count of rating_contact_id: 16

                    count of history_rating_contact_id: 60

                    count of security_id_effective_date: 60

                    count of security_id_effective_date2: 4032

                    count of benchmark_contrib_mktval_weight:4032

                    sum of benchmark_contrib_mktval_weight: 800%

Step3: User selects effective_date 2016-01-29

Note this attribute is in core_position table. core_position table is linked to core_rating table by security_id key. Further, since core_position table also contains security_id_effective date all the securities corresponding to effective date 2016-01-29 also gets selected.

                    Observations:

                    count of rating_contact_id: 13

                    count of history_rating_contact_id: 7

                    count of security_id_effective_date: 7

                    count of security_id_effective_date2: 4032

                    count of benchmark_contrib_mktval_weight:4032

                    sum of benchmark_contrib_mktval_weight: 800%

At this point following is part of the UI picture

Step4:

Notice that now there are 7 records in security_id_effective date and i am using the code

security_id_effective_date2 = p(security_id_effective_date) to narrow down the count of security_id_effective_date2 to 7.

Now, the correct answer is sum of benchmark_contrib_mktval_weight should be 6.16%.

This would be achieved if user made another selection on the UI where he/she makes the exact same selection on history_rating_contact_id field (i.e. 917 same as step 1 but this time of a similar yet different field)

Problem: User won't have history_rating_contact_id field on the UI to select so somehow using set analysis history_rating_contact_id needs to be equated to rating_contact_id

To achieve this i am writing the code

history_rating_contact_id = p(rating_contact_id)

At this point the it still doesn't arrive at the correct answer. So it looks to me that either the p function is not being interpreted or i should be using another function instead of p function.

Any suggestions to solve this?

[swuehl]

a)

You are talking about count of field values, I assume you are using something like

=Count(FIELD)

right?

Note that some of your fields are key fields between tables, and counting a key field should not be done, since the results are misleading (QV does not know which table the count should be performed in). Either count a field in a specific table, where you know there is a value when the original field shows a value or perform a Count(DISTINCT FIELD) in key fields.

Using a Count(security_id_effective_date) would explain that you get 7 returned, while the right most list box shows more distinct values.

IMO, a distinct count is what is of interest here anyway, since set analysis' field modifier are only coping with distinct field values in the symbol table.

TLDR: Could you post the same observations, but using a Count(DISTINCT FIELD)?

b)

Step 4: "Notice that now there are 7 records in security_id_effective date and i am using the code security_id_effective_date2 = p(security_id_effective_date) to narrow down the count ofsecurity_id_effective_date2 to 7."

Again, a field modifier, like security_id_effective_date2 = p(security_id_effective_date) is coping with distinct values of the symbol table, not records.

And what you probably know, you should read the line like: Take all possible symbols (distinct values) of field security_id_effective_date and try to find matching values in field security_id_effective_date2. Each matching value gets selected (in the scope of the aggregation only) , defining the scope for the aggregation you are using the set expression in, in combination with the other field modifier / set identifier etc. in your set expression.

I wrote matching values in bold letters, because I still can't see matching values in your screenshot. I believe that's the cause of your issue, not a bug in p() function or something else.

Though it's quite hard to correctly analyze what happens with all the implicite field selections etc., I believe your set expression just renders to a possible record set containing zero records (Is this what you are getting from your expression with the set expression?),  because there are no matching values when you try to use this piece of code:

security_id_effective_date2 = p(security_id_effective_date)

Hope this helps,

Stefan