topic Re: ApplyMap not working in QlikView

ApplyMap not working

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

I want to map months to quarters using this mapping table and this expression in my script: ApplyMap('MONTHQUARTER',MQLMONTHFINAL,'') where MONTHQUARTER is the name of the mapping table below. However, I keep getting blanks. Any idea what may be going wrong? The month abbreviations I am using in the mapping table are consistent with how months show up in the month field I am mapping to quarters. My guess there is some format issue (text vs. number) but I am lost. Thanks.

MQLMONTHFINAL	Quarter
Jan	Q1
Feb	Q1
Mar	Q1
Apr	Q2
May	Q2
Jun	Q2
Jul	Q3
Aug	Q3
Sep	Q3
Oct	Q4
Nov	Q4
Dec	Q4

Re: ApplyMap not working

agomes1971 — Wed, 25 Jul 2018 15:23:01 GMT

Hi,

please see below:

quick applymap example

HTH

André Gomes

Re: ApplyMap not working

Anonymous — Wed, 25 Jul 2018 15:26:42 GMT

Thanks, Andre. I am familiar with how ApplyMap works. I have been using it a lot. The syntax is clear to me. My challenge is that in this case it is not picking up any matches and just gives me blanks, which is odd.

Re: ApplyMap not working

agomes1971 — Wed, 25 Jul 2018 15:28:50 GMT

I think the best for you to obtain some help was to put here a sample of your project.

Thanks

André Gomes

Re: ApplyMap not working

Colin-Albert — Wed, 25 Jul 2018 16:10:51 GMT

If you change your expression to ApplyMap('MONTHQUARTER',MQLMONTHFINAL) or add this as an extra test expression, then the apply map with the two parameters will show the values for MQLMONTHFINAL that are not mapping to MONTHQUARTER values.

It may be a data type issue especially if MQLMONTHFINAL was created using the month() function which returns a dual value. Testing with the two parameter applymap will show this.

The other gotcha is if you created the mapping table using "load" instead of "mapping load" - does the mapping table exist in the data model after the reload has finished?

Re: ApplyMap not working

Anonymous — Wed, 25 Jul 2018 16:28:11 GMT

Colin, thanks. I did load as the mapping load but I think the data format is the issue as you suggested. I did in fact create MQLMONTHFINAL using the month () function. How do I solve that?

Re: ApplyMap not working

Colin-Albert — Wed, 25 Jul 2018 16:39:08 GMT

Try including a text function in your applymap.

ApplyMap('MONTHQUARTER', text(MQLMONTHFINAL), '')

Re: ApplyMap not working

Anonymous — Wed, 25 Jul 2018 17:31:11 GMT

I will try. Thank you!