topic Indirect Selection (P function etc.) in QlikView

Indirect Selection (P function etc.)

Tue, 09 Feb 2016 18:51:41 GMT

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

Re: Indirect Selection (P function etc.)

vgutkovsky — Tue, 09 Feb 2016 19:28:57 GMT

Kind of a loaded question--basically, it depends on how your data model is structured. I'm guessing, from your description, that Table1 and Table2 are connected somehow (i.e. that neither one is an island). If so, keep in mind that selecting a set of X values of Table1's contact_id values will have an associative impact on Table2's possible universe of contact_id values. So you may need to also ignore Table1's contact_id field in the same set analysis: sum({<Table_1.contact_id,Table_2.contact_id=P(Table_1.contact_id)>} Sales)

But really this is just a guess. If you need more help, posting screenshots of your data model and expression might help.

Regards,

Vlad

Re: Indirect Selection (P function etc.)

swuehl — Tue, 09 Feb 2016 21:19:29 GMT

Could you detail what you mean with 'it doesn't work'?

Most of the time when you get issues with p() function, you need to clear other field selections to avoid an incompatible set, just like Vlad mentioned.

You also need to assure that there are possible matches in the field value.

If you create two list boxes for the contact_id fields of both tables, then make your selections in table1 and as well all the selections you may imply in your set analysis, are there matching value possible in the contact_id of table2 list box?

Just as a side note, if you want to assign the set of selected table1 values, you could also write

{<Table2.contact_id = Table1.contact_id >}

i.e. no need for the p() function in that case ( the p() function will also assign a set when there is no selection made in table1 contact_id).

Re: Indirect Selection (P function etc.)

Tue, 09 Feb 2016 22:02:26 GMT

Hi Vlad

Yes, the two tables are indirectly connected and they are not islands by themselves.

I modified the code per your suggestion below, however it still doesn't seem to be working.

Sum({ 1< parent_clone_type = {'Parent'},

rating_contact_id,

history_rating_contact_id = p(rating_contact_id),

security_id_effective_date2 = p(security_id_effective_date)

benchmark_contrib_mktval_weight)

Following is a pic of part of the data model, you'll see core_rating and core_security_history are the two tables i am referring to core_rating has rating_contact_id which the user selects on the front end, however i need to get history_rating_contact_id selected in order to get the correct benchmark_contrib_mktval_weight sum. I've writing following two lines of codes in order to achieve it but it is still not equating history_rating_contact_id TO rating_contact_id

rating_contact_id,

history_rating_contact_id = p(rating_contact_id),

Thanks

Re: Indirect Selection (P function etc.)

swuehl — Tue, 09 Feb 2016 22:26:15 GMT

Have you tried to select the possible values manually in list boxes?

You are also modifying this date field:

security_id_effective_date2 = p(security_id_effective_date)

where you can have same issues, i.e. you may need to clear security_id_effective_date field to avoid incompatible sets.

Re: Indirect Selection (P function etc.)

vgutkovsky — Wed, 10 Feb 2016 01:15:59 GMT

Yep, add security_id_effective_date to be ignored in your set.

Re: Indirect Selection (P function etc.)

swuehl — Wed, 10 Feb 2016 11:08:20 GMT

Just noticed that you are using set identifier 1 in your set analysis, so I don't think it's a matter of clearing other fields, so your set expression can be limited to:

Sum({ 1< parent_clone_type = {'Parent'},

history_rating_contact_id = p(rating_contact_id),

security_id_effective_date2 = p(security_id_effective_date)

benchmark_contrib_mktval_weight)

Can you detail what your expected results are and what you see instead? If you get zero returned even when no selections are made. it seems to me that the combination of the field modifications are incompatible, resulting in an empty record set for the aggregation scope. I would specifically look into the impact of 'Parent' set to parent_clone_type to the possible values of history_rating_contact_id and security_id_effective_date2. There still need to be some common values with rating_contact_id resp. security_id_effective_date.

Without knowing your data model (and the possible field values), as well as why you think the expression is not working, it's hard to tell what my be wrong.

Re: Indirect Selection (P function etc.)

Wed, 10 Feb 2016 14:52:07 GMT

Hi Vlad

added security_id_effective_date to be ignored in set analysis but it still didn't work.

To explain the problem a bit differently. The user is making following two selection on the front end. (Effective Date and rating_contact_id)

However, using the set analysis i need to make selection on history_rating_contact_id.

Used following set analysis but it doesn't seen to be working.

Sum({ 1< parent_clone_type = {'Parent'},

rating_contact_id,

history_rating_contact_id = {'917'},//p(rating_contact_id),

security_id_effective_date,

security_id_effective_date2 = p(security_id_effective_date)

benchmark_contrib_mktval_weight)

Thanks

Re: Indirect Selection (P function etc.)

vgutkovsky — Wed, 10 Feb 2016 17:40:31 GMT

Ah, that's helpful. Can you do one more screenshot with the following: (1) add list boxes for security_id_effective_date2 and benchmark_contrib_mktval_weight, (2) in addition to what you have selected above, physically click (a) Parent in parent_clone_type, (b) 917 in history_rating_contact_id, and (c) 2016-01-29 in security_id_effective_date2

Thanks,

Vlad

Re: Indirect Selection (P function etc.)

Wed, 10 Feb 2016 20:15:06 GMT

Hi Vlad

Please note that security_id_effective_date2 is actually concatenation between a security id and effective date so i've selected one of the security's effective date here i.e. 203_2016-01-29. 203 being one of the securities.

Re: Indirect Selection (P function etc.)

swuehl — Wed, 10 Feb 2016 20:35:25 GMT

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)

Re: Indirect Selection (P function etc.)

Wed, 10 Feb 2016 20:38:23 GMT

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

Re: Indirect Selection (P function etc.)

Wed, 10 Feb 2016 20:42:26 GMT

Below is the screenshot with security_id_effective_date and security_id_effective_date_2

Re: Indirect Selection (P function etc.)

swuehl — Wed, 10 Feb 2016 22:18:16 GMT

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.

Re: Indirect Selection (P function etc.)

vgutkovsky — Wed, 10 Feb 2016 22:38:00 GMT

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

Re: Indirect Selection (P function etc.)

Wed, 10 Feb 2016 23:21:11 GMT

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

Re: Indirect Selection (P function etc.)

swuehl — Wed, 10 Feb 2016 23:30:57 GMT

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

Re: Indirect Selection (P function etc.)

vgutkovsky — Wed, 10 Feb 2016 23:44:42 GMT

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

Re: Indirect Selection (P function etc.)

Thu, 11 Feb 2016 17:37:14 GMT

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?

Re: Indirect Selection (P function etc.)

swuehl — Thu, 11 Feb 2016 18:38:47 GMT

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

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