topic Re: How to restrict the records in Table A using a condition in Table B, both are not directly associated. in App Development

How to restrict the records in Table A using a condition in Table B, both are not directly associated.

Koti_Ch — Mon, 14 Feb 2022 12:41:06 GMT

Hi,

Please check the below image for the data model.

Requirement: Every Project has multiple ABS, for closed projects we shouldn't show the ABS data. for e.g in a Pivot table, if we click on a project name, it expands and shows a list of abs under it (like subtasks).

So here, the Fact table is connecting both ABS and Project Master tables.

If Project Status = 'Closed', we should not show ABS data (or we should show N/A).

Please assist.

Thanks in advance.

Koteswar

Re: How to restrict the records in Table A using a condition in Table B, both are not directly associated.

chris_djih — Mon, 14 Feb 2022 14:35:01 GMT

Do you want the data not be shown (but actually data is in the app for other charts).
Or do you want the data is even not loaded if the ProjectStatus = closed?

Re: How to restrict the records in Table A using a condition in Table B, both are not directly associated.

Koti_Ch — Tue, 15 Feb 2022 06:17:41 GMT

I want the data not to be loaded if the ProjectStatus = Closed.

Re: How to restrict the records in Table A using a condition in Table B, both are not directly associated.

chris_djih — Tue, 15 Feb 2022 08:03:30 GMT

then youshould start with loading ProjectMaster table and adding this where statement to the load statement: where [Project Status] <> 'closed'; //only load Projects that aren't closed.

then you fill your fact table and add the following exists statement:
where exists(project_natural_key)
and at last you load your ABS with: where exists (abs_natural_key)

Re: How to restrict the records in Table A using a condition in Table B, both are not directly associated.

Koti_Ch — Tue, 15 Feb 2022 08:25:45 GMT

Thank you, Chris.
This is very helpful.