Dynamic data transformation

[nmodi]

so we have requirement to convert bunch of XML's into SQL however we need to transform data in between based on condition set, 

such as:

condition 1 : if clientname = 'a' and processtype = 'n' then columna = 'hello from a & some additional text'

condition 2 : if clientname = 'b' and processtype = 'm' then columnb = 'hello from b & some additional text'

condition 3 : if date between '1/1/2017' and '1/1/2018' then columng = 'hello from c & some additional text'

condition 4 : if flag = 'retrunclient' then columnx = 'hello from x & some additional text'

and so on...

we have bunch of condition like this & depending on condition, we need to update value for different column each time.

most of the XML's have different schema & depending on the XML, we can load data into appropriate sql table however we need something that we can use as generic transformation.

We dont want to write all these condition in tMap as we need some flexible solution that allow us to change above condition without changing the job.

We are using enterprise version 6.4. Is there something already available in Talend that we can use? drools? validation rules?

[nmodi]

I would really appreciate any help on this subject, really looking forward to some direction on this in Talend

[nmodi]

@xdshi I would really appreciate your on this to guide us in right direction.

[Anonymous]

Hello,

Could you please let us know  if this related topic helps?

https://community.talend.com/t5/Design-and-Development/tMap-with-Dynamic-schema/m-p/43409#M12970

Best regards

Sabrina

[nmodi]

Thanks for your response,

this is for dynamic schema, out schema is predefined however we want transform data based on given condition. and since there will be so many conditions like that, We dont want to write this in as 'if or else' condition. Is there any better way to manage this scenarios?

for example:-

condition 1 : if clientname = 'a' and processtype = 'n' then columna = 'hello from a & some additional text'

condition 2 : if clientname = 'b' and processtype = 'm' then columnb = 'any other text'

condition 3 : if date between '1/1/2017' and '1/1/2018' then columng = 'any other text'

and so on...

[akumar2301]

do you above if else conditions has any pattern or business use case defined?

if you can defined your use case and tranlate into transfomation rules , it would easy to use dynamic or automated logic. try tRules if that can help.

[nmodi]

There is no common pattern really, it could be any pre-defined condition.

I see that tRule only takes 1 or 2 field as input & process those as output. How can I use that with whole dataset?

[Anonymous]

I have a similar problem where I have an input row with around 250 columns and I need to apply over 1000 validation rules to this data. The output is a record id and an error number. Validation can be a condition of a number of input fields. I can't see any way to apply multiple conditions to an input and get multiple errors for output.

[akumar2301]

you could use tMap for this.

Develop each validation rule as Variable , 1)result variable should have error message or "" as a result .
valDef1 = ""
ValDef2 = "error"
valDef3 = ""

2) Create another var like result = valdef1+ valDef2 + Valdef3

4) create your Tmap out only if result result is not null .

It is tricky if you want to keep your validation rule outside talend job.

[Anonymous]

We want the rules to be managed by the business. We currently have a rules engine that uses java script rules but it is slow compared to embedded rules.