Two Values as one Value

[#1]

I have this this values in a database .

Denied

Deny

Paid

Pay

Reverse

Rev

I want to convert Deny to Denied, Pay to Paid and Rev to Reverse so their information merge with their respective correct value. Any idea will be greatly appreciated.

Thanks

[sunny_talwar]

May be like this using a mapping load:

MappingTable:

Mapping

LOAD * Inline [

Name, NormalizedName

Deny, Denied

Pay, Paid

Rev, Reverse

];

Fact:

LOAD allOtherFields,

          ApplyMap('MappingTable', FieldName) as FieldName

FROM Source;

Or you can do this using Match:

Pick(Match(FieldName, 'Deny', 'Pay', 'Rev') + 1, FieldName, 'Denied', 'Paid', 'Reverse') as FieldName

[Anonymous]

define a mapping table and use applymap

example:

TEST:
mapping
load * Inline [
In, Out
Deny, Denied
Pay, Paid
Rev, Reverse
];


ORIG:
Load * Inline [
Inp
Denied
Deny
Paid
Pay
Reverse
Rev
];

NEW:
Load Inp,
applymap('TEST', Inp) as Outp
resident ORIG;

]

[swuehl · Accepted solution]

I would also suggest a MAPPING approach.

It's also explained here:

Data Cleansing

[sunny_talwar]

I tried to cover all my bases this time

[sunny_talwar]

Damn it!

I though I did cover all my bases, but guess not

[#7]

But Really Helpful. Thanks!!!