1 Reply Latest reply: Feb 12, 2016 4:01 AM by Marcus Sommer RSS

    Royalty Rate

    Mick Hancock

      I was wondering if someone could help me. In short the code below is from Crystal Reports. And I'm not sure how to go about this in QlikView. I basically need to Divide the Quantity by (2.2, 1.8, 1.4, 1.5) depending on the STOCK_CATEGORY Number to get the BCM. Then multiply the BCM number by the Royalty Rate. My understanding to work out the Royalty Rate I need something like this - STOCK_CATEGORY (22, 21,20) UNIT_OF_MEASURE (Ton) Divided by 2.2 =BCM x Rate = Royalty Rate. Once I have this final figure I need the GST Value (Sum ({@Royalty})*{?GST %}/100).

       

      Also each Region has a Region Code for example:

      if
           {?Region}= [ "South West - Pearsons" , "South West - Skipworth" ,
                        "Perth - Fode" , "Perth - WA Limestone" , "Perth - Regans Ford" , "Kemerton Silica"  ,
                          "Iron Stone Valley"  , "Harris - Chapman Hill"                               ]   and {STK_MASTER.STOCK_CODE} IN ['GRBR-PITTN','GRHOEX-PITTN','GR07MA-PITTN','GR07MAEX-PITTN']
                      then
                            "BCM"  - Region Code
      else

      if
           {?Region}= [ "South West - Pearsons" , "South West - Skipworth" ,
                        "Perth - Fode" , "Perth - WA Limestone" , "Perth - Regans Ford" , "Kemerton Silica"  ,
                          "Iron Stone Valley"  , "Harris - Chapman Hill"                               ]
                      then
                            "Ton"  - Region Code

          else
          if
                  {?Region} = [   "South West - Gifford" ,  "South West – DIMASI" ,  "South West - WL Brown" ,  "South West - Brown/Martin Rd" ,
                                  "Geraldton - CRIP" , "Geraldton - LEVETT"  , "Geraldton - KRIS" , "Payton - SpringHill"   ] and {STK_MASTER.STOCK_CODE} IN ['GRBR-PITTN','GRHOEX-PITTN','GR07MA-PITTN','GR07MAEX-PITTN']
                        then
                               "BCM" - Region Code
          else
          if
                  {?Region} = [   "South West - Gifford" ,  "South West – DIMASI" ,  "South West - WL Brown" ,  "South West - Brown/Martin Rd" ,
                                  "Geraldton - CRIP" , "Geraldton - LEVETT"  , "Geraldton - KRIS" , "Payton - SpringHill"   ]
                        then
                               "m3" - Region Code
         else
           if
                          {?Region}= "Perth - HPPL"
                           then
                              "Sales"

       

       

       

      The Royalty rates:

      else

                if

                        {?Region}= "South West - WL Brown"

                               then

                                  1.35

               else

                if

                        {?Region}= "South West - Pearsons"

                            and

                                ( {STK_TRANS.STOCK_CODE} LIKE ['AGLIPE*'] )//or {STK_TRANS.STOCK_CODE} like "LIPE")

                                   and

                                     {STK_MASTER.STOCK_CATEGORY} = ['30']

                                

                               then

                                  3.00

              

               else

                if

                        {?Region}= "South West - Pearsons"

                            and

                                ( {STK_TRANS.STOCK_CODE} like ['LI14PE-PITTN','*LIPE*'] )//or {STK_TRANS.STOCK_CODE} like "LIPE")

                                   and

                                     {STK_MASTER.STOCK_CATEGORY} = ['30']

                                

                               then

                                  1.50

                else

                if

                        {?Region}= "South West - Pearsons"

                            and

                            {STK_MASTER.STOCK_CATEGORY} = ['30']

                                                          

                               then

                                  3.00

                      else

                          if

                              {?Region}= "South West - Pearsons"

                                   and

                                  {STK_TRANS.STOCK_CODE} = ['FSPE-PITTN']

                                      then

                                          1.00

       

       

      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------

       

      if
            uppercase ({STK_MASTER.UNIT_OF_MEASURE}) = ['TON' , "TN" , "T"]
              then
                    (
                          if
                              {STK_MASTER.STOCK_CATEGORY} = ['22' , '21' , '20']
                                  then
                                      // if({STK_MASTER.STOCK_CODE} IN ['GRBR-PITTN','GRHOEX-PITTN','GR07MA-PITTN','GR07MAEX-PITTN'])
                                         // then
                                             // {@QTY} / 2.2
                                      // else  
                                              {@QTY} / 1.8
                         else
                          if
                              {STK_MASTER.STOCK_CATEGORY} = ['42' , '41', '81' , '40']
                                 then
                                  {@QTY} / 1.4
                       
                          else
                                  {@QTY}
                     )
      else

              if
                   uppercase ({STK_MASTER.UNIT_OF_MEASURE}) = ['M3']
                    then
                         ( if
                              {STK_MASTER.STOCK_CATEGORY} = ['30'] AND {STK_MASTER.STOCK_CODE}='LIPE-PITMT'
                                  then
                                     {@QTY}*1.5
                           else

                                     {@QTY}
                         )

        • Re: Royalty Rate
          Marcus Sommer

          The most things here look that they are simple matchings from values to a category. Most probably I would use for this one or two additionally dimension-tables within the datamodel and by larger datasets maybe also a mapping with applymap() to the other fact/dimension-tables.

           

          Instead of this you could also use pick(match()) - Which conditional functions do you use? - within the script and within the gui to make such matchings and under all circumstances I would avoid nested if-loops.

           

          - Marcus