Define a lookup table with three columsn: field1, field2, and result.
Populate this table with records like ('AC', 'LSS', 'BACKUP CAMERA'), one record for each potential combination of field1 and field2.
Finally use a lookup transformation in your mapping to retrieve the fitting target value for each combination of field1 and field2.
I was also thinking about lookup, but I got stuck at the point "There are some thousands of records coming from source with distinct values.".
If this is really 1000 distinct value, I am afraid, we cannot avoid manual decode functions even in lookup as well.
But yeah, right now I cannot think of any better options than yours
To Nico's point you don't need a decode. The lookup is a reference table that will have all the key pairs and the value column. You populate the table once and then when you do the lookup you join on the two fields you are checking and the result is the value column. No need to do any decode's inside of the mapping.
Yes it may take some time to setup the table but it will be far easier to maintain then using a decode statement.
Lookup option I have tried it worked fine, But we wanted to automate the job without loading data to some lookup table.
Can we have option: like based on the keywords coming from source can we populate data o target?
Kind of AI or machine learning concepts do we have in power center?
As mentioned, the lookup is the easiest way to achieve what you need.
The lookup "media" (be it a relational table, a flat file, or something else which can be used as a source in PowerCenter) has to be provided once and then it should remain stable (at least until some changes in the lookup contents are needed). That's a pretty common approach for such tasks.
What exactly do you mean by "like based on the keywords... can we populate target"? It sounds as if you are not at all comfortable with a lookup, and I would like to understand what exactly is making you feel uncomfortably.