topic Re: Need help with pivot table / set analysis in dimension in QlikView

Need help with pivot table / set analysis in dimension

davymichiels — Fri, 14 Dec 2012 09:37:34 GMT

Hello

I am having some problem with displaying data in 1 table. I think pivot table is the best option.

I want to have as a result a table with products, stock and orders not yet delivered. Selection will be made on warehouse and product(s).

Problem 1: I want to see also the products that do not have any stock or orders and I want to see 1 line for each order (with order ref, and so on).

Problem 2: Stock lies in warehouses (with names of only 2 characters) and orders are placed in same warehouses (but with full name)

I have solved problem 2 by adding a variable (vWarehouse) that states if warehouse (2 characters) "WH" is selected, variable gets value "WareHouse". Then this Variable is used in set analysis to display only values for this WareHouse.

Like for balance quantity to be deliverd on the order I use :

sum({$< WareHouse={'$(vWarehouse)'}, Status={NotDelivered}>} BALANCEQTY)

So far this works

Problem 1 however I can not get passed:

I want to display all products (regardless stock or order balance qty) and if there are orders with balance, all orders with balance. But only one "cell" (or row) for each article.

Please see added file for what I mean.

I can get to same layout but then products with stock 0 are not displayed. Or same but can only get 1 order to appear per product. Or 1 row for each order. So I am kind of stuck here.

Anybody got idea how to solve this?

Thanks

Re: Need help with pivot table / set analysis in dimension

sujeetsingh — Fri, 14 Dec 2012 09:54:58 GMT

A good explanation but please try to load a small sample ...since it is very easy...and second less time consuming....

You can use set analysis.for thiss

Re: Need help with pivot table / set analysis in dimension

chematos — Wed, 26 Dec 2012 18:57:07 GMT

I think you need to create a complete structure in your script with all your products, stock and orders and give values ( usually 0 ) to orders or stock that doesn´t exist so you will always have values for all your products.

I hope this helps, regards.

Re: Need help with pivot table / set analysis in dimension

davymichiels — Thu, 27 Dec 2012 09:17:47 GMT

Hi,

Thanks for the info

I kind of did same thing last week, not in script but with "if" statement in dimension (to replace all empty values by a value) and with expression (something like "value+1") so empty values return "1" (afterwards hid this column). So there are no more empty values in dimension, and no more empty values in expression.

Only annoying thing is now 1 line with all minus-signs ("-") for every article (even those who do have orders) but result is acceptable.

regards

Re: Need help with pivot table / set analysis in dimension

chematos — Thu, 27 Dec 2012 12:08:11 GMT

Sometimes this solution have too cost to implement, but if you don´t have performance problems I think it´s an efficient solution.

Regards