- UNIFYBroker Forum
- UNIFYBroker/Aurion
-
Questions
Need to join based on a value being NULL
Aurion is giving me multiple security records per person. I have been told the only way to select the correct record is to go for the one where "Clearance Date To" is NULL. How do I do this in IdB 5?
Answer
What I want is for the Join transformation, when selecting on a Date field, to treat a later date as precedent, and a NULL date as the most precedent. Does it do that?
I'm trying various things but with the amount of data every change takes hours to test.
The Aurion query tool should allow you to do this (Filters). Otherwise select a key that provides uniqueness and use the available transformations or solution code to select the correct record.
I'm confused, why would you spend hours testing that when you could just not import the bad records?
If you must use a transformation, I'd recommend using a CSV/PowerShell connector to mock up the test cases and prove that your scenario works.
The PowerShell transformation is an option if none of them match your use case.
Because it takes hours, if not days, to work with the Aurion person to get reports configured and changed, then importing takes a long time (20 mins for this report), then I want to change something so have to do it all again, then IdB gets stuck on reflection and doesn't tell me why, so again I clear it all out and start over. This is NORMAL when you are working with real data in a real customer environment.
I would happily use the Powershell transform - but how? I need to join to data coming in from another connector. Can the Powershell transform do that? Examples would help if so.
There's an example on that page. You can do anything you like in PowerShell - for example an LDAP lookup.
I still also want to understand how the standard Join works when you do Priority selection, pick a DATE field, and don't set anything else. There are no options on whether an earlier date or a later date is the priority, and also nothing on how NULLs should be handled.
I have tried to set this up to see what will happen but it seems to kill the reflection. I left it over an hour ago with 22074 pending changes, and it is now stuck on 20957 pending changes. There are no errors in the IdB log and I've reindexed.
Customer support service by UserEcho
The Aurion query tool should allow you to do this (Filters). Otherwise select a key that provides uniqueness and use the available transformations or solution code to select the correct record.