5 Replies Latest reply on Nov 8, 2018 10:22 AM by Ken Flerlage

    Conditional Join Based on NULLS in Secondary Datasource

    Sean Miller

      Hi,

      My primary datasource in question is material master data by plant, meaning both plant and material will always be populated.

      My secondary datasource is vendor contract information, and will always contain material but will not always contain plant. This is because not all contracts will be plant-specific and can be used across all facilities the material is active.

      Problem: If a contract is plant-specific, I absolutely need the table to join on both plant and material. If a contract is not plant specific, I need it to only join on Material.

       

      I have tried a number of solutions. The closest I have come to a solution is duplicating the contract datasource and creating two separate views for plant-specific and general contracts using filters.

       

      The best way I can summarize is: IF Field 2 is Null THEN Join by Field 1 ELSE Join by Field 1 and Field 2

       

      I am about to provide an embarrassingly simple example of my question. Anyone have any ideas?

       

      Data Example:

      Primary DatasourceSecondary Datasource
      PlantMaterial
      Contract NumberPlant
      MaterialUnits Consumed
      Plant 1Shrink Packaging111Plant 1Shrink Packaging50
      Plant 2Shrink Packaging222Plant 2Shrink Packaging1000
      Plant 1Corrugate Packaging333NULLCorrugate Packaging100
      Plant 2Corrugate Packaging

       

      Ideal Output:

      PlantMaterial
      Contract NumberUnits Consumed
      Plant 1Shrink Packaging11150
      Plant 2Shrink Packaging2221000
      Plant 1Corrugate Packaging333100
      Plant 2Corrugate Packaging333100

       

      Edited to make the packaged workbook a live connection, and to provide the source sample workbook.