Apply Map

[Anonymous]

Hi

I have date field.

I extracted the month from this date using Month(Date) as named the field as My_Month.

My_Month will have the values

Jan

Feb

Mar

Apr.

I want this months to get mapped against Quarter.

So i used applymap function.

Month_Quarter_Mapping:

Mapping Load * Inline

[

Jan,Q1

Feb,Q1

Mar,Q1

Apr,Q1

May,Q1

Jun,Q2

];

Note: My requirement is Jan-May Q1.

I used

applymap('Month_Quarter_Mapping',My_Month) as My_Quarter.

My_Quarter is also showing

Jan

Feb

Mar

May.

I wanted it to show

Q1

Q1

Q1

Q1

Q2

instead.

Could anyone help me on this.

Thanks!

[venkatg6759]

applymap('Quarter_Mapping',Month) as Quarter

May be u might be missing to define the table name or something else try this

[Peter_Cammaert]

That is probably because your mapping table has no matches for the values you try to look up. If no matches are found, applymap() will return the input value which is the month name.

[Peter_Cammaert]

Most often caused by either specifying the wrong table name as a first parameter (check the name AND spelling) or because your values do not match for some reason...

This works ok:

Month_Quarter_Mapping:

Mapping Load * Inline

[

Month,Quarter

Jan,Q1

Feb,Q1

Mar,Q1

Apr,Q1

May,Q1

Jun,Q2

];

LET vTest = applymap('Month_Quarter_Mapping', 'Mar');

Best,

Peter

[stigchel]

Dates are duals, meaning they have a text and a number representation. In this case qv tries to map the number against your text values in the map. try using the text function to force qv to look at the text of date like this:

applymap('Month_Quarter_Mapping',Text(My_Month)) as My_Quarter

[Peter_Cammaert · Accepted solution]

Or replace your month names with numbers in your mapping table:

Month_Quarter_Mapping:

Mapping Load * Inline

[

1,Q1

2,Q1

3,Q1

4,Q1

5,Q1

6,Q2

];

[Anonymous]

Hi Peter Cammaert

I tried by giving month number instead of name.

Month_Quarter_Mapping:

Mapping Load * Inline

[

1,Q1

2,Q1

3,Q1

4,Q1

5,Q1

6,Q2

];

It did work.

Thanks a lot for your help!