How to ignore records in a lookup table?

[Bennie47250]

Working on a price audit file.
We have a table that stores information on product types and pricing discount percentages, similar to what I have shown below.

Note that the last line for each customer code has a blank in the Pricing Promo column and this is the only line I need to use the multiplier for the various product types.

Examples
For a customer code of 86 and a product type of LL it will use 25 as the multiplier
For a customer code of 86 and a product type of PS it will use 35 as the multiplier

For a customer code of 87 and a product type of LL it will use 24 as the multiplier
For a customer code of 87 and a product type of PS it will use 34 as the multiplier

Is it possible to condition a formula to use the multipliers shown only the line with a blank in the Pricing Promo field? If yes, how?

Thanks
Bennie

 

[CrystalizeCanada]

A formula along these lines may be what you are looking for:

if isnull({table.PricingPromo}) or {table.PricingPromo}='' then 
  select {Table.CustomerCode}
   case 86: select {Table.ProductType} case 'LL':25 case 'LS': 20 case 'PL': 32 case 'PS': 35 default: 1
   case 87: select etc...
   default: 1
 else 1

Gordon
Crystalize

 

[SkipVought]

It is not a best and accepted practice to embed data in code!

All your data ought to be in a proper table, not structured like the pivot you have posted, that makes data analysis very laborious.

[pre]
Customer Code Pricing Promo Product Type Multiplier
86 Stock LL 30
86 Stock LS 35
86 Stock PL 37
86 Stock PS 40
...
[/pre]



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]