Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mikegrattan
Creator III
Creator III

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. 

1 Solution

Accepted Solutions
sunny_talwar

May be this

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

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

View solution in original post

10 Replies
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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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

sunny_talwar

May be this

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

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

mikegrattan
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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

sunny_talwar

Yup

Check here:

Mod ‒ QlikView