8 Replies Latest reply: Sep 11, 2014 7:16 AM by Nikolaos Kavroulakis RSS

    complex apply map table


      Hi everyone,

       

      I have a set of rules that I want to apply from a mapping table. The table looks like the below one:

       

       

      MappingValuesRule1Rule2Rule3Rule4
      Value1R1MappingKeyR4MappingKey
      Value1R2MappingKeyR4MappingKey
      Value1R3MappingKey
      Value1R1MappingKey
      Value2,R2MappingKey
      Value2,R3MappingKey
      Value1,R1MappingKey
      Value3,R3MappingKey
      Value3R1MappingKey
      Value4R2MappingKeyR3MappingKey
      Value4R4MappingKey
      etc

       

       

      So I was thinking I would split the big mapping table in 4 smaller tables and thed do the mapping.

      But the issue is that each rule has to be hierarchicaly applied from top to bottom. So Value1>Value2>Value3>Value4 rules

      In addtion when one mapping rule is applied in a row the others rules on the same row should be ignored adn when the first rle applied for each mapping key is only the valid one. For example if Row1 R1MappingKey does exist in other rows after first row is applied the oders should be ignored.

       

      Any ideas on how to solve this puzzle?

       

      P.S i can not submit or open any qvw for security reasons.

        • Re: complex apply map table
          Dick Zeeman

          Is the order displayed here (Rule1, Rule2) the order of priority? E.g. if a value has an entry in Rule2 and Rule4 you would want the mapping to display the entry in Rule2?

           

          I would load the data into 1 mapping table with 4 subsequent loads:

           

          MappingTable:

          LOAD MappingValues, Rule1 As Rule

          FROM RuleTable

          WHERE Not Isnull(Rule1)

           

          LOAD MappingValues, Rule2 As Rule

          FROM RuleTable

          WHERE Not Isnull(Rule2)

          AND NOT EXISTS (Rule)

           

          LOAD MappingValues, Rule3 As Rule

          FROM RuleTable

          WHERE Not Isnull(Rule3)

          AND NOT EXISTS (Rule)

           

           

          etc

            • Re: complex apply map table

              Hi Dick,

               

              Yes if there is an entry in Rule2 and Rule4 I would want only the rule 2 to be mapped.

              The issue with you implemetation is that R1MappingKey ext can be group of values sometimes and maybe subtotal of another R*MappingKey.

                • Re: complex apply map table
                  Ruben Marin

                  Hi kavros

                   

                  There is a 3 parameter version of applymap, the 3rd parameter is when the key is not found:

                  ApplyMap('MapRule1', Key, ApplyMap('MapRule2', Key, ApplyMap('MapRule3', Key, ApplyMap('MapRule4', Key))));

                   

                  Hope this helps.

                    • Re: complex apply map table

                      Thanks for the help. I already tried that.

                      The problem is that the rules have to be applied in a top to bottom order as per row per row.

                      So what could potentialy be a solution is tho have each row as a seperate mapping table.

                        • Re: complex apply map table
                          Pradip Sen

                          As per my understanding you may try like

                           

                          Mapping LOAD

                            MappingValues&'-'& If(Len(Rule1)<>0,Left(Rule1,2), If(Len(Rule2)<>0, Left(Rule2,2), If(Len(Rule3)<>0, Left(Rule3,2), Left(Rule4,2) ))) AS Key,

                            *

                          Inline [

                          MappingValues, Rule1, Rule2, Rule3, Rule4

                          Value1, R1MappingKey, '', '', R4MappingKey

                          Value1, '', R2MappingKey, '', R4MappingKey

                          Value1, '', '', R3MappingKey, ''

                          Value1, R1MappingKey, '', '', ''

                          Value2, '', R2MappingKey, '', ''

                          Value2, '', '', R3MappingKey, ''

                          Value1, R1MappingKey, '', '', ''

                          Value3, '', '', R3MappingKey, ''

                          Value3, R1MappingKey, '', '', ''

                          Value4, '', R2MappingKey, R3MappingKey, ''

                          Value4, '', '', '', R4MappingKey

                           

                          ];

                  • Re: complex apply map table
                    vikas mahajan

                    This scenario can be handle in script it self in back end.

                     

                    Vikas

                      • Re: complex apply map table

                        I am not sure what you mean exactly.

                        Taking as an example the inline table above from Pradivs code. In the case of R1Mapping Key I will have 2 Values to map to. But I only want to take the Value1 into account.

                         

                        KeyMappingValuesRule1Rule2Rule3Rule4
                        Value1-R1Value1R1MappingKey
                        Value3-R1Value3R1MappingKey