How to create a function similar to WildMatch() or SubField()

[rdugg]

Hi,

I'm looking for a way to write a function that can be used as WildMatch or Subfield(), that can be called in a Load statement.

I understood that Subroutines cannot do that. How to do it then ?

I have a table with a large diversity of columns and depending on several of these fields value I have to go and grab a date on a specific other field out of a set of other fields, to consolidate my heterogeneous table.

So basically all my inputs required are row per row and the job could be done in a very complex Load If() as myDate

declarative statement but the complexity of the conditions chain and cases in that if() would make the code completely unmaintainable (i guess around different 16 cases) , and I don't want to go there.

I also don't want to build my table with a for loop and concatenate row by row.

[Kushal_Chawda]

@rdugg  what are you trying to achieve exactly? Could share a example with sample data?

[seanbruton]

Hi, 

What is the input data, business rules and output desired?

Regards

[rdugg]

Let's say I get a list events in a table from several CQRS microservices where each event has it's own naming (and unit system) of things. This gets me to a very heterogeneous table (with a lot of null fields for properties that does not exist depending on events) and is my input.

But for each event I can normalize its data from all the input that I have on the row.

Let's say I want to have a field in my final table for creation_time:

For an event I would get it in epoch, for another one in ISO 8601, for another one that would be the minimum value of 2 field, for another one it would be the number of seconds starting from last sunday at midnight, the year and the week number, or from a date and internet time (BMT)....

It could be color where sometimes it's sourced as RGB value, or CMYK, or HSL color space.

It's a general question.

So being able to create my own functions to use in the Load statement, as when calling WildMatch() or SubField() is what I want to achieve.

it also seems that the technique of a "for loop" concatenating each row one by one, to build a table is crappy performance wise, so that's why I would like to build my table's row "inline" calling functions from the load statement directly.

[marcus_sommer]

You could create custom functions by the use of parametrized variables but by the intended scenario it's probably just an outsourcing of the statement from the load to a previously placed variable without saving any efforts or improving the performance. Because if the required calculation is depending from record-context it remained mainly by an if-loop logic - you may get a more readable version by applying a pick(match()) for it.

Before going in this direction I suggest to consider a concatenating again - but not on a record-level else on a context-level.

Beside this hints your statement of a lot of null-fields that the source-data is (partly) a crosstable. In nearly all cases is beneficial to transform a crosstable to a regular data-structure - and applying the needed transformation on top of it.