Royalty Rate

Fri, 12 Feb 2016 01:51:28 GMT

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

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

then

1.35

else

{?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

{?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

{?Region}= "South West - Pearsons"

and

{STK_MASTER.STOCK_CATEGORY} = ['30']

then

3.00

else

{?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 — Fri, 12 Feb 2016 09:01:31 GMT

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

topic Royalty Rate in QlikView

Royalty Rate

Re: Royalty Rate