Regarding ApplyMap

[Anonymous]

Hi All,

I have a excel with contains two columns

Old Code   New Code

1000           1000A

2000            2000C

3000            3000C

The requirement was to lookup the old code and if there is a match populate the new cod in the field ProdCode

Now my question is Is it possible to store the oldcode as well in a new field.

For example : something like below:

ProdCode     ProdCodeold

1000A            1000

5000                 -

2000C             2000

3000C             3000

So the Prodcode after the applymap will contain the updated ones and we need the previouscode to be stored in ProdCodeold prior to the change.

Kindly let me know if the requirement is unclear

Thanks,

Anupama Jagan

[swuehl]

Maybe something like

LOAD

     ApplyMap('MAP', Code) as ProdCode,

     If( Len(ApplyMap('MAP',Code,NULL() )), Code, NULL() ) as ProdCodeold,

     ...

FROM ...;

[effinty2112]

Hi Anupama,

If I understand you you have a field of codes, some of which are now updated. If a newer code exists then you want to replace with the new code. If no new code then use the existing code.

If you have a mapping table of the pairs of old and new codes then

ApplyMap('MapOldToNew',OldCode,OldCode) as Prodcode

might be what you want.

Cheers

Andrew

[Peter_Cammaert]

No that is not possible! I mean your data example cannot be realised. Maybe your intention can be produced (see the suggestions by Stefan and Andrew for that matter)  but your example cannot! Nothing can never be translated into something using ApplyMap().

Producing a new code 5000 out of nothing using an Applymap() function call cannot be done. There is one sure thing you will have to stuff into a translation function and that is an actual source value. Nothing doesn't count as a source value.

Your explanation seems to indicate that you want tro replace an existing code by an updated one - if availabl - and leave the original one as-is if no update is available. That is the most basic use of an ApplyMap() call like:

LOAD Applymap('MapOldCode2NewCode', ProdCode, ProdCode) AS ProdCode, // Update original

     Prodcode AS OriginalProdCode // Keep original

FROM source.something (options);

Best,

Peter

[arvind_patil]

Hi Anupama,

just create the extra field in transaction table.

Code as ProdOld Code

Thanks ,

Arvind Patil