Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

mikegrattan
Contributor II

Complicated situation for Lookup function

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. 

Tags (2)
1 Solution

Accepted Solutions

Re: Complicated situation for Lookup function

May be this

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

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

10 Replies

Re: Complicated situation for Lookup function

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
Contributor II

Re: Complicated situation for Lookup function

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

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
Contributor II

Re: Complicated situation for Lookup function

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

Re: Complicated situation for Lookup function

May be this

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

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

mikegrattan
Contributor II

Re: Complicated situation for Lookup function

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

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

mikegrattan
Contributor II

Re: Complicated situation for Lookup function

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

Re: Complicated situation for Lookup function

Yup

Check here:

Mod ‒ QlikView

Community Browser