topic Re: Apply Map in QlikView

Apply Map

Anonymous — Mon, 15 Jun 2015 14:57:54 GMT

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

instead.

Could anyone help me on this.

Thanks!

Re: Apply Map

Anonymous — Mon, 15 Jun 2015 15:02:04 GMT

First you have to name your fields in the inline table.i.e Month,Quarter

Month_Quarter_Mapping:

Mapping Load * Inline

[

My_Month,My_Quarter

Jan,Q1

Feb,Q1

Mar,Q1

Apr,Q1

May,Q1

Jun,Q2

];

Re: Apply Map

fvelascog72 — Mon, 15 Jun 2015 15:02:41 GMT

Hi,

You can use:

'Q'&Ceil(Month(Date(YOURDATEFIELD),'DD/MM/YYYY'))/3) as "Quarter"

Re: Apply Map

jpenuliar — Mon, 15 Jun 2015 15:06:02 GMT

'Q' & Ceil(Month(Date)/3) as Quarter should do the trick

Re: Apply Map

Anonymous — Mon, 15 Jun 2015 15:09:38 GMT

But my requirement is bit different.

Jan-May as Q1

Jun-Jul as Q2

Aug-Sep as Q3

Oct-Dec as Q4

Re: Apply Map

venkatg6759 — Mon, 15 Jun 2015 15:14:23 GMT

try to define ur months in mapping load table

Mapping Load * Inline [

Month,Quarter

Jan ,Q1

Feb,Q1

..........

Jun,Q2

];

then do applymap from this table to ur calender date table

Re: Apply Map

-SW- — Mon, 15 Jun 2015 15:18:22 GMT

Jebamalai is correct, if you use his code, to add the headers,

Month_Quarter_Mapping:

Mapping Load * Inline

[

My_Month,My_Quarter

Jan,Q1

Feb,Q1

Mar,Q1

Apr,Q1

May,Q1

Jun,Q2

];

then use your code in your load statement,

applymap('Month_Quarter_Mapping',My_Month) as My_Quarter.

In front end, your My_Quarter details should display as Q1, etc.

Re: Apply Map

Anonymous — Mon, 15 Jun 2015 15:24:08 GMT

Ya i tried the same but still i am getting the Quarter as Jan,Feb,Mar,Apr,May ,Jun instead of Q1,Q1,Q1,Q1,Q1,Q2.

Re: Apply Map

-SW- — Mon, 15 Jun 2015 15:26:12 GMT

Can you post your sample qvw?

Re: Apply Map

Anonymous — Mon, 15 Jun 2015 15:28:04 GMT

can you send the updated code you are using?

Re: Apply Map

venkatg6759 — Mon, 15 Jun 2015 15:29:38 GMT

applymap('Quarter_Mapping',Month) as Quarter

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

Re: Apply Map

Peter_Cammaert — Mon, 15 Jun 2015 15:31:56 GMT

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.

Re: Apply Map

Peter_Cammaert — Mon, 15 Jun 2015 15:38:53 GMT

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

Re: Apply Map

stigchel — Mon, 15 Jun 2015 15:45:21 GMT

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

Re: Apply Map

Peter_Cammaert — Mon, 15 Jun 2015 15:58:56 GMT

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

];

Re: Apply Map

Anonymous — Tue, 16 Jun 2015 04:13:38 GMT

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!