8 Replies Latest reply: Jul 11, 2017 8:11 AM by Stephen Naude RSS

    Using applymap in expression editor

      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?

       

       

        • Using applymap in expression editor

          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

              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

                  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
                    Stefan Wühl

                    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