topic Re: Ignoring Filters in QlikView

Ignoring Filters

Tue, 25 Aug 2015 20:26:08 GMT

Our company is using Qlikview to display Inventory data. There are A LOT of fields but here is basically what I want to do.

The basic table looks like this

PART# WAREHOUSE QUANTITY YEARUSAGE

1234 24 100 0

1234 52 500 100

1234 64 0 75

1234 32 200 115

But imagine there are thousands of part#s and a hundred different warehouses. The same part #s are stored in multiple warehouses.

I am trying to find "dead inventory" (part#s in warehouse where the 'yearusage' is zero) and the find all the other warehouses that DO have usage on that same part.

I can filter the master data table to show me all the part #s in a specific warehouse that have Zero YearUsage (dead stock)

Select Warehouse = 24 and YearUsage = 0

Now I want to see all the other warehouses that carry that same part to see who DOES have usage on that part # so i can get the inventory transferred over to the warehouse that is using it.

But because I selected "Warehouse = 24" and "YearUsage = 0" I don't know how to build another table that ignores the warehouse and yearusage filters. I want to keep the Part# filter but I want the table to ignore the other 2 filters so I can see other warehouse usage and decide what to do with the "dead inventory" at warehouse 24.

HELP!

Re: Ignoring Filters

Oleg_Troyansky — Tue, 25 Aug 2015 21:36:32 GMT

Hi Campbell,

welcome to the QlikView community!

If you need to ignore certain selections, you can use Set Analysis - this is a special type of a condition that can alter the set of data that goes into the given aggregation. You will need to look up what Set Analysis is and learn the basic syntax. For your specific needs, the expression would look like this:

sum({<Warehouse=, YearUsage= >} YearUsage)

Or, if you wanted to create a list of other warehouses with YearUsage, you could do something like this:

concat( {<Warehouse=, YearUsage={">0"}>} Warehouse, ', ')

cheers,

Oleg Troyansky

Learn Advanced Set Analysis and Advance Aggregation with me at the Masters Summit for QlikView!

Re: Ignoring Filters

sasiparupudi1 — Wed, 26 Aug 2015 06:42:34 GMT

use alternate states

Re: Ignoring Filters

gautik92 — Wed, 26 Aug 2015 06:45:19 GMT

try to use alternate states

Re: Ignoring Filters

Wed, 26 Aug 2015 11:22:16 GMT

WWhat are alternate states?‌

Re: Ignoring Filters

qlikmsg4u — Wed, 26 Aug 2015 11:23:51 GMT

go through this link

what is alternate states in Qlikview ?

ALTERNATE STATES

Re: Ignoring Filters

Fri, 28 Aug 2015 14:38:55 GMT

You guys are awesome! That worked perfectly. I have some great new tools. Thanks.

I have some more questions so get ready.

Re: Ignoring Filters

MarcoWedel — Fri, 28 Aug 2015 16:27:13 GMT

maybe you could use the p() function on the PART# number field in a set expression.

regards

Marco

Re: Ignoring Filters

MarcoWedel — Fri, 28 Aug 2015 22:21:53 GMT

maybe like this:

=Sum({1<PART#=p(PART#)>} YEARUSAGE)

table1:
LOAD RecNo() as WAREHOUSE,
     Ceil(Rand()*9000)+1000 as PART#,
     Floor(Rand()*500,100) as QUANTITY,
     Floor(Rand()*100,10) as YEARUSAGE
AutoGenerate 100
While IterNo()<=1000;

hope this helps

regards

Marco