topic Re: Regarding ApplyMap in QlikView

Regarding ApplyMap

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Regarding ApplyMap

swuehl — Fri, 30 Jun 2017 19:53:27 GMT

Maybe something like

LOAD

ApplyMap('MAP', Code) as ProdCode,

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

...

FROM ...;

Re: Regarding ApplyMap

effinty2112 — Fri, 30 Jun 2017 20:52:19 GMT

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

Re: Regarding ApplyMap

Peter_Cammaert — Fri, 30 Jun 2017 23:41:27 GMT

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

Re: Regarding ApplyMap

arvind_patil — Sat, 01 Jul 2017 05:48:58 GMT

Hi Anupama,

just create the extra field in transaction table.

Code as ProdOld Code

Thanks ,

Arvind Patil