Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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}
)
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