topic Dynamic data transformation in Talend Studio

Dynamic data transformation

nmodi — Fri, 28 Dec 2018 17:22:08 GMT

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?

Re: Dynamic data transformation

nmodi — Wed, 02 Jan 2019 17:45:16 GMT

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

Re: Dynamic data transformation

nmodi — Fri, 04 Jan 2019 16:41:23 GMT

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

Re: Dynamic data transformation

Anonymous — Mon, 07 Jan 2019 02:46:30 GMT

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

Re: Dynamic data transformation

nmodi — Mon, 07 Jan 2019 15:25:38 GMT

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...

Re: Dynamic data transformation

akumar2301 — Mon, 07 Jan 2019 20:49:33 GMT

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.

Re: Dynamic data transformation

nmodi — Mon, 07 Jan 2019 20:55:42 GMT

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?

Re: Dynamic data transformation

Anonymous — Thu, 10 Jan 2019 10:15:02 GMT

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.

Re: Dynamic data transformation

akumar2301 — Thu, 10 Jan 2019 11:20:50 GMT

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.

Re: Dynamic data transformation

Anonymous — Thu, 10 Jan 2019 11:24:46 GMT

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.

Re: Dynamic data transformation

akumar2301 — Thu, 10 Jan 2019 12:17:06 GMT

Try Drool oprion

https://help.talend.com/reader/uwwCVAHxWDS6l5fZQ~lVYA/Bnm0~QJqvDLhp~AsATmITA

Apart from the Drools BRMS embedded into Talend Administration Center, you can also install and use Red Hat BRMS or JBoss BRMS to manage business rules. For further information, see Red Hat JBoss BRMS documentation.

I never used these but looks like this could solve your problem.

Re: Dynamic data transformation

Anonymous — Thu, 10 Jan 2019 14:16:42 GMT

I was trying to use tRules but it only seems to allow 2 columns in the Schema for the rule so no use if validation condition applies to more than 2 columns