Data-Manipulation when loading data

[reinholdgraf]

I like to change some data when loading it.

e.g.

Divisions come in as numbers (100,200,300).

When loading, I want to change let' say 100 to 100 - Test1, 200 to 200 - Test2, etc.

How can I do this ?

[igdrazil]

Hi Reinhold,

try something like this:

temp:

LOAD * Inline [

Nr

100

200

300];

LOAD Nr & ' - Test' & rowno() as Number resident temp;

Regards

Chris

[reinholdgraf]

ok.

But instead of Test, I will use our internal divison-names.

e.g. Poland, UK, Austria, Switzerland-Sub1, etc.

Something like an allocation-table

[igdrazil]

Are they in same order as the numbers? then u can just add these two to 1 like:

LOAD number & ' - ' & country as Division;

Regards

Chris

[reinholdgraf]

no. the names are in some cases the country, in other cases not. (historical reasons).

[igdrazil]

I guess u have to put them manually together, when they are in 2 different columns and have to shared order.

At least i have no more clue how to do this, sorry.

Regards

Chris

[hic · Accepted solution]

You can easily do this with ApplyMap.

First, you must store your division names together with division numbers. I have done this in an inline in the script, but you can use any source - a DB or an Excel sheet. Then all you need to do is to construct what you want with ApplyMap():

NameMap:
Mapping Load * Inline
[DivisionNo, DivisionText
100, Poland
200, UK
300, Austria
400, Switzerland-Sub1];

Data:
Load *,
DivisionNo & ' - ' & ApplyMap('NameMap',DivisionNo,'Unknown') as Division
From Source (...)

HIC

[reinholdgraf]

That's it.

Many Thanks