topic ApplyMap in QlikView

ApplyMap

Wed, 27 Oct 2010 03:14:47 GMT

Hi...

Can we use applymap only during load?

Can it be used in the development area? I have about 40 columns and based on the id returned, I have to choose the column name for displaying the value.

I have column names as C1, C2, C3..............................C24,C25

Now, based on the id field 1,2,3,4,....25 I have to choose the column C1,C2,C3.......C25 resp....

Can i use apply map in the expression section of a straight table?

Any suggestions how i go about this?

Regards

Kumar

ApplyMap

marcel_olmo — Wed, 27 Oct 2010 07:13:30 GMT

Sorry Thavasikumar, I don't get it. Why do you want to do it in the expression section?

In fact I've never done something like this.

In this case I always do two options :

1- Doing the ApplyMap in the Load Script.

2-Put directly the field that you wanted to be with the applymap directly on the straight table (because if the data is well loaded, it doesn't matter if you put the column of C1,...,C25 or the column 1,....,25.

PS : If you insist to do it by expression, maybe you can do a little trick and put that the column of 1,...25 is equal to left('firstColumn',1) of the column C1,....C25.

I hope it helps.

See you around!!

ApplyMap

Wed, 27 Oct 2010 14:32:34 GMT

Hi...

To explain it better,

I have a straight table with two dimensions, question id and question. I have to display the score for each of the questions. The database consists of 44 columns for the 44 questions. Now, I can manually put 44 if statements to compare the question id and get the respective question score. I wanted to know if there is an easier way like decode statement in sql.

Eg:decode(question_id,1,C1,2,C2,........,44,C44,0)

But, nothing of this form is available in qlikview and the closest thing resembling to decode was applymap but even that doesnt work as it returns C1 as a string than as a column value.

So. I am not sure if theres an alternative to this and I will have to put in 44 if...else statements to check for each of the question_ids.

Thanks for inputs.

Regards

Kumar

ApplyMap

marcel_olmo — Wed, 27 Oct 2010 14:53:44 GMT

Ok, I think I got you.

Definitely you need to use Applymap.

Make sure you're using it well.

The point of Applymap is that you have to create a mapping load table with two fields as this way :

ApplymapTable :

mapping load

firstValueToCompare,

valueToReplace

Base Table :

firstValueToCompare,

applyMap('ApplymapTable', firstValueToCompare, 'defaultValueIfNotFound') as applyMapField

(*) The order of the fields in the Mapping table (Applymap table) is important. I'm telling you because I've been stuck with that one day.

I hope that it helps!!

ApplyMap

Wed, 27 Oct 2010 16:05:06 GMT

Hi Marcel,

I used applymap in the load process and added as an additional field. But, the problem I am facing is that when I use that field in the expression of a straight table, it interprets it as a string rather than column.

Map1:
mapping load * inline [
x,y
1,C1_1
2,C1_2
3,C1_3
4,C1_4
5,C1_5
6,C1_6
7,C1_7
8,C2_1
9,C2_2
10,C2_3
11,C2_4
12,C2_5
13,C2_6
14,C3_1
15,C3_2
16,C3_3
17,C3_4
18,C3_5
19,C3_6
20,C3_7
21,C3_8
22,C3_9
23,C3_10
24,C3_11
25,C3_12
26,C3_13
27,C3_14
28,C3_15
29,C3_16
30,C3_17
31,C3_18
32,C3_19
33,C3_20
34,C3_21
35,C3_22
36,C3_23
37,C3_24
38,C3_25
39,C4_1
40,C4_2
41,C4_3
42,C4_4
43,C4_5
44,C4_6
];

Question_Desc:
load
QUESTION_ID,
APPLYMAP('Map1',QUESTION_ID) AS QID,
QUESTION,
COMPONENT_ID;
SQL select id question_id, text question, component_id from Question;

Now, I have a straight table with dimensions component_id, question and the expression as QID.

The result is C1 getting displayed when the question id is 1. I want the value of column C1 to be displayed rather than the string 'C1'. I tried using the if statement and it works but wanted to know if qlikview has an alternative to map column names similar to decodes.

Thank you.

Regards

Kumar

ApplyMap

Wed, 27 Oct 2010 16:18:58 GMT

I think you can use dual function . please refer to dual function

dual(QUESTION_ID,APPLYMAP('Map1',QUESTION_ID) )AS QID

if use num(QID) ,it will return the number ;on the other hand,if we use text(QID) ,it will return the text.

ApplyMap

Wed, 27 Oct 2010 16:48:30 GMT

The dual function will continue to return a text but qlikview wont be treating it as a column name. So, I ll still have to use the if statement to reference the column using the qid(numeric or text).

Thanks for the help.

Regards

Kumar