Change equal values to separate values in script editor

[klikgevoel]

Hi all,

Assume the following simple dataset:

[EqualValues]:
LOAD * INLINE [
ZipCode,Municipality,Region,Province,MunicipalityKey,RegionIdentifier
4061,'West Betuwe','Unknown','Gelderland','GM1960','AM99'
4063,'West Betuwe','Unknown','Gelderland','GM1960','AM99'

4147,'West Betuwe','Unknown','Gelderland','GM1960','AM99'

4121,'Vijfheerenlanden','Unknown','Utrecht','GM1961','AM99'

4143,'Vijfheerenlanden','Unknown','Utrecht','GM1961','AM99'

4231,'Vijfheerenlanden','Unknown','Utrecht','GM1961','AM99'

];

What I want to achieve is the following output:

4061,'West Betuwe','Rivierenland','Gelderland','GM1960','AM10'
4063,'West Betuwe','Rivierenland','Gelderland','GM1960','AM10'

4147,'West Betuwe','Rivierenland','Gelderland','GM1960','AM10'

4121,'Vijfheerenlanden','Midden-Utrecht','Utrecht','GM1961','AM13'

4143,'Vijfheerenlanden','Midden-Utrecht','Utrecht','GM1961','AM13'

4231,'Vijfheerenlanden','Midden-Utrecht','Utrecht','GM1961','AM13'

Since all values share an identifier, denoted by the GM****, a simple IF-statement in a chart expression suffices. However, I want to change these values directly from the script editor. Do note that the RegionIdentifier variable is optional for change, not necessary.

[asinha1991]

create a map and use it in script?

[DavidM]

The region is identified with MunicipalityKey or RegionIdentifier? You can use Mapping Load.

RegionMap:

Mapping Load:

RegionIdentifier,

Region

And then in load of the table:

Load *,

ApplyMap('RegionMap', RegionIdentifier, 'Unknown')

The way it works is you map regions to identifiers (AM99 - Rivierenland) and the you apply this map. Note, one identfier needs to have one region.

[klikgevoel]

As you can see the region is identified by the RegionIdentifier (ie the AM99) in this case. The AM99 simply stands for "we do not know the region". However, based on the MunicipalityKey, which has two different instances among the equally shared RegionIdentifier, we're able to solve the Region to which it belongs. As such, the MappingTable does not suffice, if I understand your proposed solution, in this example. Since MunicipalityKey GM1960 belongs to RegionIdentifier AM13, otherwise Region "Rivierenland" and the other to AM10, "Midden-Utrecht".

I'm hoping for a solution, if it will work in the script editor, to make a synthetic key out of RegionIdentifier and MunicipalityKey. For example:

RegionIdentifier&'-'&MunicipalityKey AS [SYNKEY]

Afterward providing a function in the script editor like: SYNKEY == 'AM99-GM1960' THEN Region IS "Rivierenland" OR SYNKEY == 'AM99-GM1961' THEN Region IS "Midden-Utrecht" 

Such a simple statement can easily be put inside a chart function. But that doesn't solve my problem.

[DavidM]

You can create Map from two fields, like this:

RegionMap:

Mapping Load:

RegionIdentifier&'-'&MunicipalityKey AS Region,

Region

And then in load of the table:

Load *,

ApplyMap('RegionMap', RegionIdentifier&'-'&MunicipalityKey, 'Unknown')

However in the mapping table you need to identify AM99-GM1960 as "Rivierenland" etc. I suppose you have a table from which you can load this

[klikgevoel]

Hmm, so for instance if you've got +350 distinct values this means that you need to specify each distinct value with its corresponding Region name? 

Example,

GM0001-AM01, 'Name1'

GM0002-AM02, 'Name2'

GM0180-AM14, 'Name3'

GMXXX-AMXX, 'NameY' 

This is a cumbersome proces...

[DavidM]

I guess you have that information in some table from where you can load it - that's how mapping is done.As long as you have table (or can build via joins) where are all three values you're good. Otherwise you would need to create this yourself