Using applymap in expression editor

[#1]

Hi all,

I 've a little question.

When you create a pivot table you are able to create expression.

In the expression editor you can choose to use a function. I try to use applymap but it doesn't work.

I do a mapping load in the script, say :

MAP_TEST:

MAPPIG LOAD

A,

B

RESIDENT TEST_TABLE

I create a pivot table with a calculated dimension and in the expression editor i would like to get the description of my dimension so i would like to use applymap.

Problem is that writing applymap('MAP_TEST', PIVOTDIM,'NOT FOUND') doesn't work because i think my map_test is no more in memory.

Mapping load are dumped when the script finished the LOAD.

Any idea why applymap is a function available in the expression editor?

Have we to make a new mapping load in the design part?

I cannot do my mapping in the script because the pivot table play with dynamic column.

I cannot use set analysis too because i want a description that is not in a interconected table.

Final, i would like to do a lookup but i'm not able.

Any idea?

[#2]

Hi Marc,

An mapping load is creating a temptable that is beïng deleted at the end of your script.

So when you are working in your frondend it does not really exists anymore.

So if you wanna do this you have to do this in the script already.

Succes, Halmar.

[#3]

I knew that but i wonder why the function is available in the expression editor.

It should not be available if you cannot use it.

Do you know how i can in an expression use a function to make a select of a description in an other table that is not linked to the model ( an orphan table).

Set analysis doesn't work because of the orphan table.

I can't resolve it in the script.

Here is my problem:

I got a customer category table and a sales table.

I've created a pivot table with the dimension category_id and with an input field we can truncate the category_id from 1 to 10 digit.

I would like to get the description of the trucated id and so make a kind of lookup in an orphan table.

Example:

Sales :

Category_id item_id Amount

311              A          100

31               B           150

If i chose in my input field only 1 position my category_id dimension is calculated with left(category_id,1)

so my pivot table say :

Category_id   Amount

3                  250

the thing is that i would like to get de description of category_id 3 and it is available in an orphan table Category.

So my idea was to add in my calculated dimension an applymap based on orphan table Category.

Any idea?

[#4]

Hi,

I know a solution.

You make table with every possible CatergoryCode and a description:

ID     Description     InputValue

3        A                    1

31      B                    2

311    C                    3

4          AA               1

41         BB               2

411       CC               3

Than if you select the value 1, the right description is shown.

Succes!

[swuehl · Accepted solution]

If your fields in the orphan table have only unique values for description and category id (this is important! - but I assume that's the case in your case), you could use an expression like this to retrieve the description from an orphan table.

=Fieldvalue('Category.Desc',FieldIndex('Category.CategoryID', left(category_id,1) ))

where Category.Desc resp. Category.CategoryID are your fieldnames from the ophan table and the second parameter to Fieldindex should return the truncated number you want to look up. I assumed left(category_id,1), but you might have to adjust this to your needs.

Hope this helps,

Stefan

[#6]

Thank you !

It works perfectly and i can use the technique for other orphan table, those used for my dynamic dimension.

Have a nice evening !

Marc

[#7]

Hi All,

I have an Orphan table

Min	Max	1st Rate	2nd Rate
0	500	5,00%	4,00%
500,01	1000	4,75%	3,80%
1000,01	1500	4,51%	3,61%
1500,01	2000	4,29%	3,43%
2000,01	2500	4,07%	3,26%
2500,01	12000	3,87%	3,10%
12000,01	15000	3,68%	2,94%
15000,01	20000	3,49%	2,79%
20000,01	100000	3,32%	2,65%

I then have a sum of Sales calculation that i need to evaluate against this table and return 1st and 2nd rate, i havn't a clue where to start, please can anyone assist?

[swuehl]

I would suggest to create a new thread for a new topic.

Maybe it's applicable to use INTERVALMATCH LOAD prefix here:

IntervalMatch ‒ QlikView

[#9]

Thanks but that wont work because the data coming in is too granular to be mapped, only once the aggregation is performed in a chart must the mapping occur. Thanks for the Help.