Find & Replace during data load

[DMG]

Hi is it possible to perform a find and replace (exact match) as part of the data import process, for example

We currently do a select * from a ms sql server view to pull all fields across,

assuming there was a field called "Entity" in that list, is it possible to replace the text inside that row of data for that field where it matches a condition, e.g.

If Entity = "Primary Security Team", replace the words "Security Team" (as an exact match) with Unit, so for those rows of data the entity value would be "Primary Unit" but for all other rows the existing value would be used ?

[MayilVahanan]

Hi @DMG 

Try like this

If(Wildmatch(Entity, '*security team*'), replace(Entity, 'Security Team', 'Unit'), Entity) as Entity

[DMG]

where in the data load statement would i place that, for example if our data load statement is currently

LIB CONNECT TO 'TEST (iuser_uid)';

[viewQS_TEST_Scope]:
SELECT *
FROM dbo.viewQS_TEST_Scope;

does it just go below this in the data load statement or do i need to place it inside the select ?

[MayilVahanan · Accepted solution]

Hi @DMG 

select *, case when Entity like '%security team%' then replace(Entity, 'Security Team', 'Unit') else Entity end as EntityNew

FROM dbo.viewQS_TEST_Scope;

or

Load *,

If(Wildmatch(Entity, '*security team*'), replace(Entity, 'Security Team', 'Unit'), Entity) as EntityNew;

SELECT *
FROM dbo.viewQS_TEST_Scope;