Custom Transformer and Looping

Hey, could you show us an example of expected output you want ?

There are so many cases where loop inside custom transformer can be avoided and simplified. Maybe you don’t actually need one ?

Hello, thank you for your quick response. I have put together an example.
Initial situation: Base table to Update over Reference

Id;Value
1;A
2;A
3;C
4;B
5;X
 

Reference table:
Id;old;new
1;A;B
2;C;D
3;B;X

After 1st update

Id;old;new
1;A -> A becomes B
2;A -> A becomes B
3;C -> C becomes D
4;B -> B becomes X
5;X -> X remains X

However: Sentences 1 and 2 are assigned the old value B.

Therefore, a second update is necessary (loop) so that the B values from 1 and 2 are also converted to X.
It is not known how often old values (A->B) are assigned during the update (as in sentence 4). Therefore, the loop must be run through x times until all old values actually only contain the new values.
Thanks for your help.

One more note: Perhaps there is also a solution using Variablesetter and variableRetriever.

I would let FME process the Reference table, so it turns

A → B, C → D, B → X

into

A → X, B → X, C → D

This can be done with an InlineQuerier, using recursive SQL.

This can also process more difficult situations (which you probably have):

A → B, C → D, F → X, B → F will become A → X, B → X, C → D, F → X

SQL query in the Inline Querier:

WITH RECURSIVE results AS (

    SELECT ID_Reference, ID_Old, ID_New FROM Data

  UNION

    SELECT c.ID_Reference, c.ID_Old AS ancestor, d.ID_New AS descendant

    FROM results c

    JOIN Data d ON c.ID_New = d.ID_Old

)

SELECT * FROM results;

See the example in the attached workspace (FME Form 2023.1)

Hello geomancer,

Thank you for the example. Sorry for not explaining my example clearly. The IDs used to recursively change the records have no meaning in terms of content. The content is only replaced for the values specified in the reference table old->new. So there is no record identifier. 
Therefore, my solution must set all records, regardless of ID, from old values to new values in multiple passes (loops).
I was also unable to use your solution in my FME. I am still working with FME 2022 and the InlineQuerier 2023.1 used is unfortunately not compatible. But I did learn about a new transformer and may be able to use it at some point.

Best regards

I think your explanation was clear, and mine wasn't 😉

My example workspace only processes the reference table. It does need a unique indentifier in the Sampler, but if your table doesn't contain such an attribute you can add it.

You should be able to enter the SQL I provided in your own InlineQuerier in FME 2022.

Hello geomancer,

Sorry, I'm really a beginner and haven't used InlineQuerier yet.
I tried to use InlineQuerier, but I get an error message (see image).
About the sampler: I don't understand. You write that the sampler needs a unique ID. I don't have one, so I can't add it and generate an artificial key in advance? That doesn't make sense to me.
All I need is a loop that runs through a bookmark (that's my Customer Transformer) multiple times. The control should (ideally) be done via VariableSetter and VariableRetriever. That's all. If necessary, I could use a user parameter to determine how often the loop should run.
Thank you.

Hi fbeginner,

No problem.

Try changing Output under Tabelle into Data.

Maybe I should have added a screenshot of the settings of the InlineQuerier.

Wow, that's the solution, thank you very much.
May I ask you for a little extra help later? 

Of course!😀

Thanks, see you later.

While I completely agree with your approach, and would have done much the same, there’s one slight difference I would use: If these are actual database tables, I’d do this in the database itself. That would prevent FME having to download both datasets, create a temporary database on disk, then execute the sql. So if the dataset is large, that would mean a huge performance improvement (if we’re talking a few thousand rows you’d probably not notice much of a difference). RDBMS’ses are meant to do this kind of thing, it’s their whole purpose (it’s not a coincidence that Safe creates a temporary database under the hood to implement the InlineQuerier!).

Generally I try to avoid the InlineQuerier if I can (of course it isn’t always possible, and I am using it in one process, but still...), and let my databases do the heavy lifting if possible.

Hello s.jager,
It could indeed lead to problems, as the amount of data is quite extensive. This means that with a large data set, the operation is transferred to the database. I would then have to use an SQLExecuter or SQLCreator and the SQL from the InlineQuerier. How would the recursive SQL look in this case? As a beginner, I don't quite understand what the reference table is in the InlineQuerier and where the data comes from. 
Best regards 

Hello geomancer,

I should really start another post here. But you have just given me another opportunity to come back to you for help. Can you describe a workspace that contains a custom transformer that is called any number of times within a single execution? I have set up a scenario:

A data record contains four columns: Run; ID, Year and Age. 

Run; Year; ID; Age (starting situation)
0;2025;1;17
0;2025;2;31
0;2025;3;45

I now want to age the data records (persons) and do this for x years. 
After the first run, I get:
Run;Year; ID; Age
1;2026;1;18
1;2026;2;32
1;2026;3;46

After the second run, I get:
Run;Year; ID; Age
2;2027;1;19
2;2027;2;33
2;2027;3;47
etc.
The ageing should be done in a custom transformer. The output is always written to a file containing all data records, i.e. the data records are always recalculated and appended, i.e. for each run. Control (end condition) is performed using VariableSetter and VariableRetriever.
I know that's a bit cheeky. I should attend a training course. But the community is also a great source of information, as the solution to the first problem shows. I hope I'm not asking too much now.
Many thanks

Yes. It depends on your situation, but most likely an SQLCreator.

Exactly the same, except for the tablename. So in the sql statement that Geomancer provided, you would have to replace “Data” with <schemaname>.<tablename>, so something like dbo.MutationTable, or whatever it is called. Everything else can stay the same.

I think what you mean is just the name Geomancer gave the table you called “Reference table” in your second post in this thread. The data is just created in the workspace as an example, based on the example you gave. If you click with your right-mouse-button on Reference Table in the workspace Geomancer provided, you can then select Expand Bookmark and see what is going on in there.

Hello s.jager,
Thank you very much for your reply.

Why make it so difficult?

This can easily be done without a custom transformer and without using a VariableSetter / VariableRetriever duo; just use a Cloner and an AttributeManager. And for flexibility use a UserParameter for the number of years to process.

Custom transformers, VariableSetter and VariableRetriever certainly are interesting elements of FME, but  they are completely unneccessary here.

Hello geomancer,

Thank you for your support. I have the impression that I still need to learn about a few more transformers, and the given example can be solved optimally in this way.
However, my example was not meant to be a real scenario; I just wanted to construct something to learn about the possibilities of VariableSetter/Retriever and Custom Transformer in application.

Many thanks

Now you have changed the text of the original post, making the whole question unclear. Please don't do that.

With VariableSetter you can keep one value, and use it later in the workspace. The VariableRetriever can get that value later on. But there is a catch: the VariableSetter MUST be processed before the VariableRetriever. In the example below the value is transferred from one flow to another.

I have learned you need to be very careful when using VariableSetter and VariableRetriever. It is very possible that the VariableRetriever is processed before the value is set in the VariableSetter. And in that case the value will not be (and can not be) read by the VariableRetriever. Therefore I don't like using them in complex workspaces.

Hello, thank you very much for the example and the practical tip regarding the use of variables. I think I will refrain from using them in complex preparations as well.
Thank you very much.