topic Re: Using applymap in expression editor in QlikView

Using applymap in expression editor

Thu, 13 Oct 2011 10:41:02 GMT

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

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?

Using applymap in expression editor

Thu, 13 Oct 2011 12:52:47 GMT

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.

Using applymap in expression editor

Thu, 13 Oct 2011 13:23:07 GMT

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?

Using applymap in expression editor

Thu, 13 Oct 2011 13:50:53 GMT

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!

Using applymap in expression editor

swuehl — Thu, 13 Oct 2011 13:55:50 GMT

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

Using applymap in expression editor

Thu, 13 Oct 2011 14:45:47 GMT

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

Re: Using applymap in expression editor

Tue, 11 Jul 2017 09:33:18 GMT

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?

Re: Using applymap in expression editor

swuehl — Tue, 11 Jul 2017 11:46:48 GMT

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

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

IntervalMatch ‒ QlikView

Re: Using applymap in expression editor

Tue, 11 Jul 2017 12:11:33 GMT

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.