Pick? Match? If?

[cbaqir]

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

[mphekin12 · Accepted solution]

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

[cbaqir]

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

[mphekin12]

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.

[cbaqir]

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.

[effinty2112]

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