transform map dilema

[El Cuchi]

Hi all,

i trust this email finds you well.

here is what i need. I do have a custom table call 'cust_table', that contains 3 fields. 'ID', 'name', 'active'.

i would like to update table via import set as follows.

- when there is a match name will update

- when source has an ID that does not exist in 'cust_table' a new record will be created in the target table.

- when a record in the target table 'cust_table' does not have a match in the source, it will set the active field to false.

At the moment, i created the data source and transform map. mapped fields with ID as coalesce and the first 2 points are covered.

what i cannot do is the last point so ID no longer in use are set to false in the target table.

source (xls file) has columns ID, name, active.

would you help me please.

regards.

[JackieZhang]

you can add new customer field in target table named last updated.  default it to false on before transform map. then if there is new update from source , set it to true. then in completed transform map script to set all the last updated = false to inactive. 

[Bhuvan]

@El Cuchi 

For your requirement, create onBefore or onStart transform script to check if the source do not have corresponding record in 'cust_table' and if no match can be found using coalesce, set the active field to false.

Thanks,

Bhuvan

[El Cuchi]

Hi All,

thanks for your suggestions.

the final solution was to create an OnStart transform script to set active to FALSE for all records. Then, the mapping would take care of the rest.

thanks

[Bhuvan]

@El Cuchi 

If my post helped you, please accept as solution so that question can be marked as answered and can help other community members.

Thanks,

Bhuvan