Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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!
use alternate states
try to use alternate states
WWhat are alternate states?
You guys are awesome! That worked perfectly. I have some great new tools. Thanks.
I have some more questions so get ready.
maybe you could use the p() function on the PART# number field in a set expression.
regards
Marco
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