topic List with excluded values in QlikView

List with excluded values

Wed, 09 Feb 2011 01:01:35 GMT

Hello,

I have some sample data that looks like the following:

Item Number	Item Type	Work Order
XYZ	CON	100101
ZYX	SHP	100103
YYZ	ROT
XYZ	CON	100104

I want to be able to create a List box that shows only the Item Types that have a work order.

I used the expression: If(Len(WorkOrder)>0,ItemType)

Which worked, expect then if I selected the CON Item type it would select on both ItemType and the WorkOrder numbers. I want it to select only on ItemType.

Any suggestions?

Thanks,

-Ken

List with excluded values

Anonymous — Wed, 09 Feb 2011 01:08:22 GMT

Ken,

If you add a Max() function around the WorkOrder field it'll just pick the latest work order ID instead of focusing on both, so this should work:

If(Len(Max(WorkOrder))>0,ItemType)

List with excluded values

Wed, 09 Feb 2011 01:20:32 GMT

Hi Johannes,

I gave that a try and it blanked out my list - showing no values. I tried both Max and MaxString (seem to get different results with them sometimes).

Just to confirm, I should be putting the expression into the General Field?

-Ken

List with excluded values

johnw — Wed, 09 Feb 2011 01:22:14 GMT

I recommend creating an "Item Type with Work Order" field during the load. It simplifies and speeds up things like list boxes and charts that want to only display item types with work orders.

,if(len("Work Order"),"Item Type") as "Item Type with Work Order"

List with excluded values

Anonymous — Wed, 09 Feb 2011 01:25:26 GMT

Ah, you're doing it as a listbox expression. In that case, try the following:

=aggr(If(Len(Max(WorkOrder))>0,ItemType), ItemType)

Not necessarily the neatest way to accomplish this but it should do the trick.

EDIT:

And with my comment on this not being the neatest approach, I definitely agree with JohnW that it's preferable to resolve this on the script side to reduce the complexity and calculations on the layout side.

List with excluded values

Wed, 09 Feb 2011 01:41:43 GMT

John: I considered that, but I'm joining results from two different ODBC data sources.

The first one contains: WorkOrder/ItemNumber

The second one contains: ItemNumber/ItemType

Is it possible to still do what you're suggesting in the script?

Johannes: That did the trick.. however if I select an item type, then only the currently selected value is visible. The others are all hidden instead of just going grey. Is there a way to avoid that (if only for future reference)?

-Ken

List with excluded values

johnw — Wed, 09 Feb 2011 02:17:04 GMT


KenMKFC01 wrote: John: I considered that, but I'm joining results from two different ODBC data sources.
The first one contains: WorkOrder/ItemNumber
The second one contains: ItemNumber/ItemType
Is it possible to still do what you're suggesting in the script?

Easy! (OK, John, then why is your post so long? We'll get to that.)

If the item number is in the work order table, then it has a work order. So you can just do this:

WorkOrders:
LOAD
WorkOrder
,ItemNumber
,ItemNumber as ItemNumberWithWorkOrder
FROM your ODBC source
;

It's denormalized to load it there, but that's not typically a problem for QlikView. If you want to renormalize the information anyway, perhaps just for the experience of doing so, you can. One "obvious" way is to join the values onto the Items table, continuing from the script above with this:

Items:
LOAD
ItemNumber
,ItemType
FROM your ODBC source
;
LEFT JOIN (Items)
LOAD DISTINCT
ItemNumber
,ItemNumberWithWorkOrder
RESIDENT WorkOrders
;
DROP FIELD ItemNumberWithWorkOrder FROM WorkOrders
;

But that left join with distinct is inefficient. Fortunately, there's a simpler and more efficient way.

Items:
LOAD
ItemNumber
,ItemType
,if(exists(ItemNumberWithWorkOrder,ItemNumber),ItemNumber) as ItemNumberWithWorkOrder
FROM your ODBC source
;
DROP FIELD ItemNumberWithWorkOrder FROM WorkOrders
;

That's probably what I'd use. But there's a way that might be slightly more efficient still, and also doesn't denormalize the data on the WorkOrders table even temporarily. Not that that matters, but just for fun:

WorkOrders:
LOAD
WorkOrder
,ItemNumber
FROM your ODBC source
;
ItemNumbersWithWorkOrders:
LOAD num(fieldvalue('ItemNumber',iterno())) as ItemNumberWithWorkOrder
AUTOGENERATE 1
WHILE len(fieldvalue('ItemNumber',iterno()))
;
Items:
LOAD
ItemNumber
,ItemType
,if(exists(ItemNumberWithWorkOrder,ItemNumber),ItemNumber) as ItemNumberWithWorkOrder
FROM your ODBC source
;
DROP TABLE ItemNumbersWithWorkOrders
;

I suspect it is faster to generate the extra table than it is to put an extra field on the WorkOrders table IF (and only if) there are a large number of work orders compared to the number of item numbers. Hard to say how it would play out in practice, and like I said, I probably wouldn't take it this far. The idea of the last script amused me, though.