topic Pick? Match? If? in QlikView

Pick? Match? If?

cbaqir — Thu, 17 Dec 2015 15:49:38 GMT

There can be a 1 to many relationship between ACTIVITY_CODE and REQUEST_ID.

The functionality I am looking for is that the user can select a REQUEST_ID (for example, 171440) and then can see all Requests with that share the same ACTIVITY_CODE (101016456).

IDEALLY, I would like to see all of the financial calculations associated as well, but I am not sure that is possible so I would be happy with the screenshot below if 171440 was the REQUEST_ID selected.

Not even sure where to start with this one but I would prefer to do it within the chart as opposed to the load, if possible, because I have several other joins happening.

Thanks,

Cassandra

Re: Pick? Match? If?

mphekin12 — Thu, 17 Dec 2015 16:03:45 GMT

Have a look at the 'Table with Set Analysis' object as see if that solution work for you?

Re: Pick? Match? If?

cbaqir — Thu, 17 Dec 2015 16:14:43 GMT

Thanks! Not sure if there would be a way to get the financials to apply...

Re: Pick? Match? If?

mphekin12 — Thu, 17 Dec 2015 16:34:21 GMT

What if you just plug in the formulas that you are using in your 'Financial Details by Project ($000) - Capital' table? I updated the 'Table with Set Analysis' table and added the first four of your formulas and the totals seem to match.

Re: Pick? Match? If?

cbaqir — Thu, 17 Dec 2015 16:41:39 GMT

It looks like it's only returning data for the selected REQUEST_ID of 171440 and/or repeating it for every row. I think the actual Lifetime Actuals should be 10,752k for 101016456. Let me look at the data and see if this is even possible.

Re: Pick? Match? If?

effinty2112 — Thu, 17 Dec 2015 16:43:43 GMT

Hi Cassandra,

As far as I can tell in your data set the relationship between distinct Activity Codes and Request ID is not how you describe.

=Max(Aggr(Count(DISTINCT REQUEST_ID),ACTIVITY_CODE)) returns 1 so no ACTIVITY_CODE has more than 1 REQUEST_ID.

However some REQUEST_ID have more than one ACTIVITY_CODE.

REQUEST_ID	ACTIVITY_CODE
173687	101016191
173687	101016193
173687	101016197
173687	101016198
173687	101016199
173687	12101020201301
173687	12101020400701