I have two files that I need to join together and I an not exactly sure how to do it.
FILE-A, is a file that contains a list of assets, and accompanying information. As part of its information, FILE-A includes fields BUDGET-NUMBER and DATE-CREATED.
FILE-B is a file that contains information that is relevant to a specific budget number. As part of its information, FILE-B includes fields BUDGET-NUMBER, BUDGET-START-DATE, and BUDGET-END-DATE.
Our budget numbers can be reused, so if I look up an asset, and want to find the budget that it belongs to, I can join FILE-A.BUDGET-NUMBER to FILE-B.BUDGET-NUMBER.
This may bring me back several FILE-B records, so in order to find the correct FILE-B record, I need to locate the FILE-B record where FILE-A.DATE-CREATED is between FILE-B.BUDGET-START-DATE and FILE-B.BUDGET-END-DATE.
To make things even more complicated, file FILE-A contains reserved asset records that have an ASSET-NUMBER assigned, but they are not currently being used, so they have a blank ASSET-NAME field.
I do not want users to do these steps, so how can I do this in OBIEE?
How do I ...
1. Exclude the FILE-A records that have a blank ASSET-NAME field
2. Filter the FILE-B records so that I only return FILE-B records where FILE-B.START-DATE <= FILE-A.DATE-CREATED and
FILE-B.END-DATE >= FILE-A.DATE-CREATED