topic Re: Complicated situation for Lookup function in App Development

Complicated situation for Lookup function

mikegrattan — Wed, 13 Sep 2017 15:42:32 GMT

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.

Re: Complicated situation for Lookup function

sunny_talwar — Wed, 13 Sep 2017 15:57:00 GMT

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')

Re: Complicated situation for Lookup function

mikegrattan — Wed, 13 Sep 2017 16:08:58 GMT

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?

Re: Complicated situation for Lookup function

sunny_talwar — Wed, 13 Sep 2017 16:14:55 GMT

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?

Re: Complicated situation for Lookup function

mikegrattan — Wed, 13 Sep 2017 16:17:43 GMT

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

Re: Complicated situation for Lookup function

sunny_talwar — Wed, 13 Sep 2017 16:27:27 GMT

May be this

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

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

Re: Complicated situation for Lookup function

mikegrattan — Wed, 13 Sep 2017 16:32:50 GMT

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?

Re: Complicated situation for Lookup function

sunny_talwar — Wed, 13 Sep 2017 16:36:53 GMT

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

Re: Complicated situation for Lookup function

mikegrattan — Wed, 13 Sep 2017 16:38:05 GMT

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

Re: Complicated situation for Lookup function

sunny_talwar — Wed, 13 Sep 2017 16:40:26 GMT

Yup

Check here:

Mod ‒ QlikView

Re: Complicated situation for Lookup function

mikegrattan — Wed, 13 Sep 2017 17:40:39 GMT

Works perfectly. Thanks!