List with excluded values

[#1]

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

[Anonymous]

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)

[#3]

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

[johnw]

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"

[Anonymous]

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.

[#6]

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

[johnw]

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.