topic Re: warehouse capacity by location range in QlikView

warehouse capacity by location range

Tue, 10 Mar 2015 11:17:47 GMT

I have a report that I had help with a week or so ago to report the percentage capacity as a gauge. This worked great but I need to expand it more and I am not quite sure how to do this.

We have 2 warehouses and I want to show the capacity percentage for each warehouse plus an overall percentage. At the moment I have 3 gauges all with the same calculation in them.

How would I separate the 2 warehouses to show the capacity percentage for each?

I know the aisle numbers for each warehouse.

Warehouse 1 goes from A01B01L1 to A49B07L2

Warehouse 2 goes from A50B01L1 to A82B02L2

Could I use a variable to identify warehouse 1 and warehouse 2? How would I incorporate this into the existing scripts on the gauges?

I have attached the qvw file.

I have a spreadsheet that is part of the qvw file that currently has all the locations at warehouse 1 and I am in the process of doing the same for warehouse 2.

I have restricted the aisle range in the script to only include these 2 warehouses.

The location list box shows all locations( currently for warehouse 1) and the table is showing the quantity of each

Thanks

Steve

Re: warehouse capacity by location range

jpenuliar — Tue, 10 Mar 2015 11:42:38 GMT

Hi Steve,

You can make use of ApplyMap() function.

This is similar to excel vlookup.

You already know the list of Aisle Numbers (Location), use this to make a lookup file (xls,txt,qvd) with 2 fields,

Aisle Number (Location) and Warehouse #.

Re: warehouse capacity by location range

jpenuliar — Tue, 10 Mar 2015 11:45:14 GMT

I noticed from your script that you are loading "Location" from a csv file,

you can probably ask your IT guys to add the Warehouse number into that file

Re: warehouse capacity by location range

Tue, 10 Mar 2015 11:46:19 GMT

ok, how would that incorporate into the existing expression in the gauges to give me a gauge for warehouse 1 and warehouse 2?

Re: warehouse capacity by location range

Tue, 10 Mar 2015 11:47:08 GMT

I am the IT guy...so adding warehouse 1 and 2 to the list isn't a problem.

Re: warehouse capacity by location range

jpenuliar — Tue, 10 Mar 2015 11:58:13 GMT

Will that be like Bottom (WH1) and Top(WH2) Warehouse in the Gauges?

It seems you already are familiar with SET Analysis, you can add the mapped field (Warehouse #) as a SET Modifier in your expression.

Count of Distinct Pallets in each Warehouse where Qtyleft > 0,

divided by

Count of Distinct Locations in each Warehouse, multiply by vmaxnumberperbay

Re: warehouse capacity by location range

Tue, 10 Mar 2015 12:17:42 GMT

Hi Jonathan

I would love to take credit for that but I had a lot of help on that by other members of the Qlikview forum.

Yes Bottom(WH1) and Top(WH2) will work. I have amended the spreadsheet so that I can pull in the extra field Warehouse.

Following what you have said my expression looks like this:

=num((count({<QtyLeft={">0"},Warehouse={WH1}>} distinct PalletNo)) / (count({<Warehouse={WH1}>}DISTINCT Location) * vMaxNumberPerBay), '#0.00%')

The gauge has changed for bottom warehouse so I think this is working. Let me check the top warehouse aisles and bays, add them to the spreadsheet and test.

I will get back to you.

Thanks Steve

Re: warehouse capacity by location range

Tue, 10 Mar 2015 15:28:39 GMT

Hi Jonathan

I think that has now worked. I am just waiting for another post I made to be approved after moderation as I copy and pasted my expression into it so you could tell me if I have got it right.

I am not getting any errors so I hope so.

Thanks for your help.

Steve

Re: warehouse capacity by location range

jpenuliar — Tue, 10 Mar 2015 15:50:12 GMT

Cool mate,

I merely relayed the logic I pictured in my head, the expression formula was still yours.

Another way to do is use of Flag Fields like "_Flg_WH1" and "_Flg_WH2", whatever works for you.