Applying logic to attribute field within a field

[#1]

Hi I have what I think is referred to as an attribute field.

So I have a field called FIELD_NAME which contains various fields such as Region, Director and Company Name.

The values of these fields ( ie Region, Director and Company Name )are in a separate field called FIELD_VALUE.

My question is how would I apply logic ie a standard pick wildmatch statement to say the region field where FIELD_VALUE of Region is equal to Belgium Province, Belgium District then show Region as ‘Belgium’ and where FIELD_VALUE is equal to Japan then Region should show as ‘Asia’.

Have got the below statement but could do with help tweaking it up as I think it’s incorrect.

Pick(WildMatch((FieldName = 'Region' AND FieldValue ='Belgium Province', 'Belgium District’,’Japan')
, 'Belgium*', 'Japan*')+1, 'Belgium', 'Japan') as REGION_NEW

[MK_QSL]

Provide sample data along with the expected output... Thanks

[Digvijay_Singh]

I think you can use mapping table where you can map currently available field values to new descriptions you want to map.

[tresesco · Accepted solution]

May be like:

If ( FieldName = 'Region' , Pick(WildMatch( FieldValue, 'Belgium*',’Japan*')
,'Belgium', 'Japan') ) as REGION_NEW

[#5]

Perfect ... thanks for your help

[#6]

Hi I thought this was correct but noticed that some of the values are mixed up ie some values that are meant to show Belgium instead show Japan and vice versa. Also the values are not aggregated so instead of just seeing two rows ie Japan and Belgium I am seeing multiple rows for these entities.

Any help would be most appreciated.

[tresesco]

Try to share sample data and expected output against that.

[#8]

Apologies as frustrating it is Im unable to post data for this.

Is there a differences between the type of match functions which may be more explicit ? It seems like the wildmatch is recognising a word which may exist in one of the values then matching that up.

For instance if I have

'All of Belgium' - expecting to see 'Belgium'

'All of Netherlands' - expecting to see 'Netherlands'

I am instead seeing some rows where I have ' All of Belgium' showing as Netherlands 

[tresesco]

Putting '*' at both ends should do the job, like:

If ( FieldName = 'Region' , Pick(WildMatch( FieldValue, '*Belgium*',’*Japan*')
,'Belgium', 'Japan') ) as REGION_NEW

And yes, sample data means sample that could have been in a simple excel or csv.