Hi All, Thank you for your immense support & help. I am stuck at this issue where I have a dataset with first 2 columns like mentioned below & need Compiled_Jurisdictions column with the help of mapping data given below: ATR_UIDJurisdiction_AbbreviationsCompiled_Jurisdictions1002314__HPCBQCQuebec1004728__HBUVBCBritish Columbia1005152__HBKQONOntario1005190__HPOCBQCQuebec1008249__HBCAQBCBritish Columbia1013668__HBCZACA,ON,BCCanada, Ontario, British Columbia1070291_4200037_HBEZCA,ABCanada, Alberta I have a Jurisdiction mapping file as well which is: Value in NowExpected values ONOntarioQCQuebecCACanadaBCBritish ColumbiaABAlbertaNSNova ScotiaMBManitobaSKSaskatchewanPEPrince Edward IslandNLNewfoundland and LabradorNBNew BrunswickNTNorthwest TerritoriesYTYukonNUNunavut so, how to solve this problem as i tried Find & replace tool but it's not compiling various jurisdiction in a single row. Thank you in advance

Replacing Abbreviation with a full name

Hi All,

Thank you for your immense support & help.

I am stuck at this issue where I have a dataset with first 2 columns like mentioned below & need Compiled_Jurisdictions column with the help of mapping data given below:

ATR_UID	Jurisdiction_Abbreviations	Compiled_Jurisdictions
1002314__HPCB	QC	Quebec
1004728__HBUV	BC	British Columbia
1005152__HBKQ	ON	Ontario
1005190__HPOCB	QC	Quebec
1008249__HBCAQ	BC	British Columbia
1013668__HBCZA	CA,ON,BC	Canada, Ontario, British Columbia
1070291_4200037_HBEZ	CA,AB	Canada, Alberta

I have a Jurisdiction mapping file as well which is:

Value in Now	Expected values
ON	Ontario
QC	Quebec
CA	Canada
BC	British Columbia
AB	Alberta
NS	Nova Scotia
MB	Manitoba
SK	Saskatchewan
PE	Prince Edward Island
NL	Newfoundland and Labrador
NB	New Brunswick
NT	Northwest Territories
YT	Yukon
NU	Nunavut

so, how to solve this problem as i tried Find & replace tool but it's not compiling various jurisdiction in a single row.

Thank you in advance

Workflow

Datasets

Developer Tools

Input

Common Use Cases

Preparation

Data Investigation

Accepted answers

Qiu

@kushalb
We should use the option "Replace Multiple Found Items" and replicate a new column for output.

1019-kushalb.yxmd

All comments

Qiu

@kushalb
We should use the option "Replace Multiple Found Items" and replicate a new column for output.

1019-kushalb.yxmd

bkurt

First do a text to columns and separate with delimiter commas into three columns. Then do three joins with the mapping file into the three columns.

binu_acs

@kushalb One way of doing this

Cobine_jursidictioons_1019.yxmd

Emmanuel_G

@kushalb

When you want to replace a value in many rows based on some mapping, the Find and Replace tool is the most suitable tool ( Instead of do if with many subconditions ).

Find in attachement the way to do that with this tool and let us know if it worlks as you want.

Replacing Abbreviation with a full name.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703