Hello,
in trying to implement an 'elegant' ifempty-then-this-else-that logic on multiple fields, I was unable to find an equivalent to the SQL 'COALESCE' functionality.
The context is as follows, but I can imagine a lot of different situations where this functionality could be useful:
In the source dataset on which I apply my transformations, I receive a certain key that might have a number of formats - e.g. a given column X might contain 'alpha' or 'random text (alpha)' or 'This line concerns beta (2021)'. Hence, I derive a number of temporary fields on this columns which I use as "candidate foreign keys" to perform a merge of alpha or beta in another table.
Something like this:
+-------------------------+------------------+------------------+------------------+---------------+---------------+---------------+
| COLUMNX | CANDIDATE_KEY_1 | CANDIDATE_KEY_2 | CANDIDATE_KEY_3 | JOINRESULT_1 | JOINRESULT_2 | JOINRESULT_3 |
+-------------------------+------------------+------------------+------------------+---------------+---------------+---------------+
| | | | | | | |
| alpha | alpha | alpha | | | 1 | 1 |
| | | | | | | |
| random text (alpha) | | alpha | | | 1 | |
| | | | | | | |
| This line concerns beta | | | beta | | | 2 |
+-------------------------+------------------+------------------+------------------+---------------+---------------+---------------+
To derive the final JoinResult field, I currently see no other option than 'ifempty(JOINRESULT_1,ifempty(JOINRESULT_2,JOINRESULT_3),JOINRESULT_1 )' which certainly works, but is difficult to maintain (and expand with more fields). In a SQL world, this could be achieved with 'coalesce(JOINRESULT_1 ,JOINRESULT_2 ,JOINRESULT_3 ). Is this possible in Easymoprh? If not, could it be added to the roadmap?
Thanks!
Many thanks!
