Straight Table - Supressing Rows

[#1]

Hello,

I'm having difficulty conditionally suppressing a row in a straight table.

I've created a sample application that illustrates the problem. There are two main tables: WorkOrders and Orders and a third that joins the two together.

A WorkOrder can be associated to zero or more Orders. An Order can be associated to zero or more WorkOrders.

I'm trying to create a straight table that lists all of the WorkOrders and only one associated Order. If there are multiple orders then it should pick the one that is of type 'Dis'. Here is some sample data:

The line with the red arrow should be suppressed because the WORKORDERNUMBER/SEQUENCENUMBER combination already has a DIS order showing for it.

The closest I have come to making this work is associate a weighted value to ORDERTYPE and then use FirstSortedValue() in the expression to return the correct one. However, as soon as I start filtering on ORDERTYPE it would show the incorrect value.

I then tried to use set analysis on FirstSortedValue() to ignore the ORDERTYPE, however the ORDERTYPE filter still didn't work correctly. It would always show the correct value, but it wouldn't suppress it.

I've created a sample .qvw file to illustrate the problem.

Thanks,

-Ken

[amars]

Hi Ken,

Couldn't understand ur problem do u mean to say that all item's with OrderType dis need to be suppressed.

Thanks

[#3]

Hi Ken,

cant understand ur probs.... think use 'distinct' in ur exp

[#4]

Hello Amars,

Not quite, no.

If there is only one Order for a WorkOrder then it should be displayed - whatever it is.

If there are multiple Orders for a WorkOrder then it should only show the one that is of type Dis and suppress the Req.

-Ken

[#5]

Hello Srividhya,

I'm not sure how DISTINCT would help? I need to conditionally suppress a line that is already unique. Hopefully my comment to amars helps to clarify what I'm after.

-Ken

[johnw]

I believe the attached file at least explains the problem a little better. Basically, I implemented the "solution" that you tried, and described why the results were wrong, and which rows needed to be suppressed and when. Please let me know if I've understood the problem.

I think I understand conceptually how to solve it, but now I need to figure out how to translate my basic idea into actual code. No guarantee that it'll work, but I'm hopeful.

[#7]

Hey John,

It looks to me that your understanding of the problem is correct!

I'm currently looking at the following article and hoping that it might help: http://www.wipfli.com/BlogPost_QTBlog_05_06_10.aspx

Possibly by ranking the orders by type and then using a calculated dimension to only show the top order. Might be a long shot..

Thanks,

-Ken

[johnw · Accepted solution]

Attached is one solution. It took me less time than I expected. Basically, the chart has to behave as if only some of the rows of data actually exist, specifically those rows with the lowest order type sequence. So I use script to add a flag that identifies those rows. The colunns then take the form of only({<Flag={'Y'}>} Field), keeping only those rows by using set analysis. Here's the script beyond what was in my previous post:

LEFT JOIN (WorkOrder_Order)
LOAD ORDERNUMBER,ORDERLINE,ORDERTYPE as TEMPORDERTYPE
RESIDENT Orders
;
LEFT JOIN (WorkOrder_Order)
LOAD ORDERTYPE as TEMPORDERTYPE,ORDERTYPESEQUENCE as TEMPORDERTYPESEQUENCE
RESIDENT OrderTypeSequence
;
LEFT JOIN (WorkOrder_Order)
LOAD
WORKORDERNUMBER
,SEQUENCENUMBER
,min(TEMPORDERTYPESEQUENCE) as TEMPORDERTYPESEQUENCE
,'Y' as MINORDERTYPESEQUENCEFLAG
RESIDENT WorkOrder_Order
GROUP BY WORKORDERNUMBER,SEQUENCENUMBER
;

My earlier thought was to do all of that work in the set analysis itself, but it seemed easier to just do it in the script, and it will be faster to execute in the chart as well.

Now if you only need those specific rows EVERYWHERE, then you'd change the last left join into an inner join to get rid of the other rows, and even a simple table box would display the right data, no expressions required.

Oh, drop the temp fields at the end. I forgot to do that, but I'm too lazy to go back.

[#9]

Worked like a charm, John.

I've decided to leave the data in, in case I need it for other reasons in the future.

I ended up using a calculated dimension to display the ORDERNUMBER if MINORDERTYPESEQUENCEFLAG is 'Y', and then suppressing the row if the value was null. This simplified the expressions.

Thanks!

-Ken

[johnw]

Yeah, a calculated dimension tends to be easier than applying the same condition to multiple expressions. In my experience it has also generally been slower to run. But as long as you have no performance problems, the calculated dimension is probably the right way to go. Glad it worked for you.