Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

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