Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Royalty Rate

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}
                   )

1 Reply
Highlighted

Re: Royalty Rate

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

Community Browser