Complicated situation for Lookup function

[mikegrattan]

I have a table with item numbers, descriptions, and a attribute called Cartons to Pallet.  This attribute tells me how many cartons of a particular item number will fit on one pallet.  If an order has less than this number of cartons, then it is a "picked" item, meaning it had to be removed from a full pallet in order to fulfill the order.

When identifying a picked item in an order I am using the Lookup function to set a flag for that item.  As of this moment, it is only checking to see if the number of cartons is less than the cartons to pallet attribute:

If(UNITS < Lookup('CartonsToPallet','ItemNumber',ITEMNUM,'ItemDescriptions'),1,0) AS PickFlag

I would also like to check if the number of cartons is greater than Cartons To Pallet but also divisible by a whole number.  For example, if Cartons To Pallet is 56, and the number of cartons on the order is equal to 56 * 2, or 56 * 3, or any other whole number then this would indicate that whole pallets of this item were loaded and the cartons did not need to be picked. 

[sunny_talwar]

Not completely sure, but you can use Mod function to find if the number is whole number or not

If(Mod(Number1, Number2) = 0, 'whole pallet', 'Incomplete pallet')

[mikegrattan]

Thank you for the response Sunny.

I think I should clarify my question.  If the number of cartons is greater than Cartons To Pallet and it is evenly divisible by Cartons To Pallet (but not necessarily just a whole number).

For example: If there are 112 cartons on the order and Cartons To Pallet for that item number is 56, then there are two full pallets on the order.  If the number of cartons is 150, then there is one full pallet and one partial pallet.  Since one pallet was "picked", then the pick flag would be 1 for that item number.

Is there a way to write my IF statement so that it accommodates both the situation where the number of cartons is less than the Cartons To Pallet value, but could also be greater than that number if it is evenly divisible by Cartons To Pallet?

[sunny_talwar]

Which part of your expression is number of cartons and which one is Cartons To Pallet here?

UNITS < Lookup('CartonsToPallet','ItemNumber',ITEMNUM,'ItemDescriptions')

Is UNITS number of cartons? and Lookup Cartons to Pallet?

[mikegrattan]

Correct; UNITS is number of cartons and I am using the Lookup function to get CartonsToPallet.

[sunny_talwar · Accepted solution]

May be this

If(UNITS < Lookup('CartonsToPallet','ItemNumber',ITEMNUM,'ItemDescriptions') or

Mod(UNITS, Lookup('CartonsToPallet','ItemNumber',ITEMNUM,'ItemDescriptions')) <> 0,1,0) AS PickFlag

[mikegrattan]

That's probably on the right track but doesn't account for the UNITS being evenly divisible by CartonsToPallet.  I wonder if it would work like this:

If(UNITS < Lookup('CartonsToPallet','ItemNumber',ITEMNUM,'ItemDescriptions') or

Mod(UNITS / Lookup('CartonsToPallet','ItemNumber',ITEMNUM,'ItemDescriptions')) <> 0,1,0) AS PickFlag

Can I divide UNITS by the lookup value and test the MOD of that?

[sunny_talwar]

Mod is checking for remainder ... You don't need to use /...

[mikegrattan]

So MOD is doing division by CartonsToPallet?  Cool....I will test and get back to you!

[sunny_talwar]

Yup

Check here:

Mod ‒ QlikView