topic Straight Table - Supressing Rows in QlikView

Straight Table - Supressing Rows

Fri, 04 Mar 2011 02:00:21 GMT

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

Straight Table - Supressing Rows

amars — Fri, 04 Mar 2011 06:36:29 GMT

Hi Ken,

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

Thanks

Straight Table - Supressing Rows

Fri, 04 Mar 2011 06:51:12 GMT

Hi Ken,

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

Straight Table - Supressing Rows

Fri, 04 Mar 2011 19:07:28 GMT

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

Straight Table - Supressing Rows

Fri, 04 Mar 2011 19:11:22 GMT

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

Straight Table - Supressing Rows

johnw — Fri, 04 Mar 2011 20:00:15 GMT

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.

Straight Table - Supressing Rows

Fri, 04 Mar 2011 20:19:56 GMT

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

Straight Table - Supressing Rows

johnw — Fri, 04 Mar 2011 20:33:21 GMT

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.

Straight Table - Supressing Rows

Fri, 04 Mar 2011 23:51:37 GMT

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

Straight Table - Supressing Rows

johnw — Sat, 05 Mar 2011 00:00:08 GMT

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.