Complicated MAPPING and tmap exppression

Seven Stars

Complicated MAPPING and tmap exppression

I have a mapping here and I'm thinking of how can I transform the data in a right way using tmap expression, I will explain what I want to do, but below is the tmap:

 

image.pngThat tmap shows very simple but, the data samples are

regnumber - 123456, 123457,123458 with dash between 3 numbers, so all my data contains 6 characters/integer.

regcode - this is the code like 1,2 or 3

regaddress, - 3 kinds of values

 

1. the usual or right address

2. merging - meaning the registered address merged on other regnumber

3. Close - the account or regnumber is closed.

 

regbranch 3 kinds of values:

 

1. usual branch on where the user registered

2. refer to reg_number 123-456 or refer reg_number 123-456

3. (Move Agency  to 123-456)

 

regkey - unique key 

regremarks - this should contains data from regaddress and regbranch if the values is not the usual add or branchmeaning the values is on2and 3 kinds of values

regstatus - chould have the data from regaddress  if merging then MG, if Closed then CL else Active - I did this already

regflag -  not so important.

 

 

My question is how can I do this transformation on expression.

 

 

if regbranch values is not a usual branch name either on this 2: 

 

2. refer to reg_number 123-456 or refer reg_number 123-456

3. (Move Agency  to 123-456)

 

Then the regaddress is close don't save and put the regbranch values to regremarks then put the regbranch and regaddress to '' or null.

 

if regadress is merging and the data is either on this 2:

 

2. refer to reg_number 123-456 or refer reg_number 123-456

3. (Move Agency  to 123-456)

 

if the number is refer to123-456  or any refer to (123,457,123-458) then remove the dash and search if the 123456 is existing or exists on regnumber column, then put the 123456 on reg_refer_number column. But if not exists then make the regaddress null and put the whole : Refer to including the numbers on regremarks column.

 

 

if the number is  (Move Agency  to 123-456)  or any Move Agency to (123,457,123-458) then remove the dash and search if the 123456 is existing or exists on regnumber column, if exists then put the 123456 on reg_agency_number column. But if not exists then make the regaddress null and put the whole : (Move to Agency to including the numbers on regremarks column.

 

Second twists is that:

 

For example 123456 (for Refer to) values has been added on the next delta run, then the 123456 should be added or modify on reg_refer_number as this is existing or exists now. -

 

same with another one:

if 123456 (for Refer to) values has been added on the next delta run, then the 123456 should be added or modify on reg_agency_number as this is existing or exists now.

 

If 123456 deleted then modify it again and remove the values on reg_refer_number and reg_agency_number.

 

 

i'm thinking if how can I do this using tmap expression and using filtering or catch output, etc.

 

can someone enlighten me or give a sample idea on how can I do this?

 

 

 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog