Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Regarding ApplyMap

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

4 Replies
swuehl
MVP
MVP

Maybe something like

LOAD

     ApplyMap('MAP', Code) as ProdCode,

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

     ...

FROM ...;

effinty2112
Master
Master

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
Partner - Champion III
Partner - Champion III

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
Partner - Specialist III
Partner - Specialist III

Hi Anupama,


just create the extra field in transaction table.


Code as ProdOld Code


Thanks ,

Arvind Patil