wildmatch replace

[JonathanKelly]

I am trying to replace an number within a data set but only when it meets a certain criteria. I am having to use 2 separate tables as the data is stored in independent areas.

My script example is as below:

Test:

 Load

JobNo

JobRef

JobStart

From 1st QVD

outer join

Load

JobNo

CompanyCode

From 2nd QVD

Jointable:
Load
If(WildMatch(JobRef,'*ABC*'),Replace(CompanyCode,'126','134'),
If(WildMatch(JobRef,'*XYZ*'),Replace(CompanyCode,'126','125'),CompanyCode)) as NewCode
Resident TEST

I am trying to replace the codes where the job ref meets criteria of containing a value as these have been miss assigned but with a consistency in the ref allowing me to identify which need to go where. 

When I run this and filter for job ref it returns every Code instead of just replacing the one one code. 

[Or]

It'd help if you include some sample data and expected results here, otherwise it's difficult to figure out what exactly should be happening and what the problem might be.

[JonathanKelly]

This is an example of the data tables I'm using, I'm trying to join these two tables while replacing the company code of '126' on the basis when Job Ref meets certain criteria. i.e ABC in the Job Ref always returns '134' instead of whatever is stored and XYZ in the Job Ref returns '125'. Hope this makes more sense 

[Or]

It doesn't look like your Jointable actually reads anything from your existing table except the NewCode field, which is not joined to the original merged table in any way. Perhaps that might be your problem?

Your expected outcome also doesn't seem to match your formula - Job 4 should remain 125 as it does not match the Replace() condition (126 should be replaced with 134, but 125 should not).

Load *, If(WildMatch(Ref,'*ABC*'),Replace(Company,'126','134'),
If(WildMatch(Ref,'*XYZ*'),Replace(Company,'126','125'),Company)) as NewCode INLINE [
Job, Ref, Company
1, 1ABC, 126
2, 2DEF, 126
3, 3MNO, 126
4, 4ABC, 125
5, 5XYZ, 126

];

[JonathanKelly]

Your solution bases off the use of Inline where you're manually inputting the data correct? Where would I load the 2 unique QVD's that hold the list of data for Ref and Company in reference to the replace script? 

Yes that was my mistake I typed 125 instead of 126 for job 4, apologies. 

The Inline would work if I was manually inputting the data source it seems however the list of Jobs and Refs to go through is in the 100's or 1000's+? 

[Or · Accepted solution]

I used an inline because I didn't have your source data, but it doesn't actually matter how the data comes in as long as it is correct.

Note that if you want the correct result here, you'll need to DROP TABLE TEST, otherwise you'll end up with both sets of data (but only the latter will have NewCompanyID).

I'm not seeing anything wrong with the formula - as you saw with the inline, it works correctly - so if there are any issues, you'll probably want to look into whether the join is being applied correctly.

[JonathanKelly]

I missed the drop table, thank you for spotting that