ETL Import - Criteria to skip import to one table but not all

DylanBlumenberg
Tera Contributor

Hi everyone,

 

I have an ETL Definition that takes in a CSV file and processes it to the ast_contract, alm_hardware, and clm_m2m_contract_asset tables in that order.
Before the info is imported, I have a few checks that I want to do but I'm struggling to find out how to do this. 

1. On the CSV, if the Service Tag field is blank, skip that row for alm_hardware but don't skip the row for ast_contract or clm_m2m_contract_asset. 

2. On the CSV, if the Asset Type field is "Workstations" or "Notebooks", set the PO Number to "94904" for ast_contract.
3. On the CSV, if the Service Tag doesn't correspond to an existing asset record serial number, do not create a new asset.

 

From my understanding, the onBefore and onAfter scripts on the ETL definition apply to the data as a whole, so if I skip a row for alm_hardware, it also skips that row for ast_contract and clm_m2m_contract_asset.

 

I also understand that ETL definitions don't use transform so I can't run a script there. The only other scripting area I'm seeing is RTE conditional scripts, but I'm thinking those area conditional on that RTE running at all, not to transform data. 

 

If those thoughts are accurate, how would I accomplish this?

2 REPLIES 2

pavani_paluri
Giga Guru

Hi @DylanBlumenberg ,

 

Please add RTE Conditional Script to skip If service tag is empty.

In the field map of PO number, add rule to update it for specific asset types.

Please set the hardware mapping to Update only , not “Insert new”. Use Service Tag as the unique ID.

 

Mark it helpful if this helps you to understand. Accept solution if this give you the answer you're looking for
Kind Regards,
Pavani P

Hi Pavani, thanks for the reply. For the field map rule, where is that done? 

When I got to RTE Field Mappings, this is what I see.

DylanBlumenberg_0-1752268524756.png

 

Or this for ETL Entity fields

DylanBlumenberg_1-1752268584716.png

 

Regarding the hardware update vs insert, I don't see any options for update. For coalesce, all I see is create, reject, or ignore.

DylanBlumenberg_2-1752268716202.png

 

On IntegrationHub, I get match true or false. False of course creates duplicates.

DylanBlumenberg_3-1752268732761.png