Filter a list based on a calculated data

[#1]

HI All,

Please help me as I am stuck.

I have 3000 products. For each product I have a minimum_stock and a current_stock. I can calculate the dimension as current_stock-minimum stock but when I want to filter on values less than 0 (for stock refill) I don't get a clear list of products that require refill.

I was wondering if anybody cold give me a simple syntax for loading the current_stock-minimum_stock as stock_move and than filter the stock_move.

Thanks!

[swuehl]

Maybe like this in your load script?

LOAD

     product,

     minimum_stock,

     current_stock,

     current_stock - minimum_stock as stock_move,

     ...

FROM ...;

[#3]

Thanks swuehl,

what if the minimum_stock and current_stock are in 2 different source tables?

[#4]

Guys, anybody, help?

[#5]

Are there any key fields connecting them? can you post a picture of your data model?

[swuehl]

Since your calculations work in the front end, your two tables seems to be linked somehow.

You can potentially join the two tables, creating a resident table that contains all fields needed for your calculation.

Instead of joining, you can and should also consider a MAPPING approach or a lookup using lookup() function.

Search the Help or the forum for APPLYMAP and / or MAPPING LOAD

[#7]

I have the product ID in both tables so QV automatically makes the connection.

Prod_ID, minimum_stock in one table and Prod_ID, current_stock in the other table.

I need to have a table that has also the minimum_stock-current_stock calculated.

[#8]

Yes, the Prod_ID.

[#9]

you could try:

TempTable:

Inner Join ("Table with minimumstock")

Prod_ID,

current_stock

Resident "Table with current_stock";

FinalTable:

Load

Prod_ID,

minimum_stock - current_stock as stock_movement

Resident TempTable;

Drop TempTable;