topic Filter a list based on a calculated data in QlikView

Filter a list based on a calculated data

Mon, 09 Jun 2014 15:09:51 GMT

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!

Re: Filter a list based on a calculated data

swuehl — Mon, 09 Jun 2014 15:14:47 GMT

Maybe like this in your load script?

LOAD

product,

minimum_stock,

current_stock,

current_stock - minimum_stock as stock_move,

...

FROM ...;

Re: Filter a list based on a calculated data

Mon, 09 Jun 2014 15:18:28 GMT

Thanks swuehl,

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

Re: Filter a list based on a calculated data

Mon, 09 Jun 2014 16:34:01 GMT

Guys, anybody, help?

Re: Filter a list based on a calculated data

Mon, 09 Jun 2014 16:35:34 GMT

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

Re: Filter a list based on a calculated data

swuehl — Mon, 09 Jun 2014 16:38:44 GMT

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

Re: Filter a list based on a calculated data

Mon, 09 Jun 2014 18:39:21 GMT

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.

Re: Filter a list based on a calculated data

Mon, 09 Jun 2014 18:40:06 GMT

Yes, the Prod_ID.

Re: Filter a list based on a calculated data

Mon, 09 Jun 2014 18:45:01 GMT

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;