Flexi JOIN

Hi,

a conditional lookup can have multiple lookups underneath it and you can control which one gets used. Usually you would do ‘take the first non-empty value’ and order your lookups so your primary source of data is first, your second option second, etc.

The example below is somewhat arbitrary, but the point is you can edit a conditional lookup and change the ‘Multiple lookup fields’ setting and have different Joins for each underlying lookup.

Hi ​@rory.smith 

thanks for the input.

I tried, but the thing is that is the same value in the same table I am looking up. Its scattered contract data in SAP that is spread over multiple IDs, sometimes the document ID, sometimes the contract-workspaceId and sometimes the internal ID, so I really need the OR statement in the JOINS. 

Any ideas? 

I was thinking creating the same view (3 times) and do it like you suggested, with the first non empty value, but the problem is that is a couple of million rows and it will slow things down a lot.

Hi,

I am not sure I get what you mean by “but the thing is that is the same value in the same table I am looking up”. Your SQL example would, after fixing the multiple ONs which you cannot do in SQL Server syntax, pull the [BUYER_GROUP] from any row in b where at least one of your OR-ed ON clauses matches.

This is no different from a Conditional lookup field with multiple underlying lookups:

This construct will store the result of the first equality search in BUYER_GROUP in table a if it returns a result. If it does not, it will consider opt2 and subsequently opt3 if opt2 returns NULL. 

If you can determine from data content in table A that a specific lookup would be the correct one, you can use conditions to influence which lookups are considered. If you are actually wanting to do something different like having the results of the different JOINs, you will need multiple Conditional Lookup Fields.

If the cardinality of the tables A and B is not 1-many, you will need to look at the Operator applied to the separate lookups.

Hi ​@rory.smith,

Sorry I didn’t give enough context, I just shared the code to hide some confidential data, but the lookup destination table, is like I mentioned SAP Contract numbers (a custom SAP environment) and there are 3 fields

So imagine ,
 

In the destination table these are scattered like this 

 

🗂️ Destination Table 

In the destination table, these IDs are also being looked up various sources and they are in the same field.

So in your example above I do get the hits BUT only on the actual first NON EMPTY value, but it doesn’t fallback to the values that might have multiple matches. 

So when I write the code munually, this is the output I need, because in the lookup table the contract number could be a hit both for the first and second ID. 

SELECT a.SCATTERED_ID, b.CONTRACT_NUMBER
FROM destination_table a
JOIN lookup_table b
  ON a.SCATTERED_ID = b.ID1
  OR a.SCATTERED_ID = b.ID2
  OR a.SCATTERED_ID = b.ID3;

What you are suggesting, I believe it does this “under the hood” 

SELECT 
  a.SCATTERED_ID,
  COALESCE(b1.CONTRACT_NUMBER, b2.CONTRACT_NUMBER, b3.CONTRACT_NUMBER) AS CONTRACT_NUMBER
FROM destination_table a
LEFT JOIN lookup_table b1 ON a.SCATTERED_ID = b1.ID1
LEFT JOIN lookup_table b2 ON a.SCATTERED_ID = b2.ID2
LEFT JOIN lookup_table b3 ON a.SCATTERED_ID = b3.ID3;

So it only returns the first match, but sometimes multiple are possible in the same line inside the lookup table, it obviously doesn’t rollback to the second possibility as well. 

Hi,

If you have multiple contract_numbers for one scattered_id your cardinalities are not laid out in a way that would work for conditional lookups and you would need to do something like: https://legacysupport.timextender.com/hc/en-us/articles/115001394043-Exploding-tables-using-views or use your syntax in a view which you can either use directly or Table Insert to a physical table or use a persisted view: 

Yes, I am on legacy (v20.10...), so I don’t have the persist view option 

I have tried fixing the cardinality of the contract table, but I am already at 43M rows, doing something like partitioning over, to get a separate row for each ID for each contract ID will be over a 100M rows and then I am getting performance issues and insane load times. 

I am limiting it per period, to execute in batches and then glue it back together at a later stage but that also then costs me a lot of performance.

Therefore I was wondering if the OR option somehow exists and if someone had similar issues. 

Anyways, thanks for the time, appreciate it!

ps - just a general remark, don’t use TX legacy with SAP data haha 😝

Hi,

TX legacy works “fine”™ with SAP as long as you use Theobald to extract and reduce your extractions to exactly what you need. The key does lie in how process registrations are implemented, if there is too much flexibility it can be expensive.

In your case it might be good to investigate whether you can have a view prepared on the SAP side that does some of what you need. The load for SAP is generally small compared to needing to pull all the detail data out and reconstructing the logic on the DWH side.

Hi ​@mmax77 do the above comments answer your question? If so please help us by marking a best answer above. If you have any follow up questions please let us know