4 Replies Latest reply: Oct 22, 2012 5:57 PM by perry.qv

# Ranking only fields that have values

Hi All,
I'm not quite sure how to word this question but basically I'm building a series of Line charts that plot test results of products over time. Each "Passing ...mm" size needs to be plotted on an individual chart, and the charts displayed on the page in order of the largest through smallest "Passing ...mm" size. Each chart contains the Test # as the x-axis dimension, and the y-axis will display two expressions: 1. is the actual test result, and 2. is the target result. This target result varies for different products but the same "Passing ...mm" size.
Below is a table of an example set of 'Targets' for five different products. The No. of "Passing ...mm" values that any particular product may have is five so... I plan to have five line charts on my dashboard, displayed one above the other.
If I just focus on the first three charts to explain, then as I select each product, I'd like the "Passing ...mm" field being referenced to change
If the user selects 'Product A' then;
Chart 1 = Passing 63mm,
Chart 2 = Passing 53mm,
Chart 3 = Passing 37.5mm
If the user selects 'Product B' then;
Chart 1 = Passing 63mm,
Chart 2 = Passing 37.5mm,
Chart 3 = Passing 19mm
...
If the user selects 'Product E' then;
Chart 1 = Passing 63mm,
Chart 2 = Passing 26mmmm,
Chart 3 = should be blank (calculation condition unfulfilled)
ProductPassing 63mmPassing 53mmPassing 37.5mmPassing 26mmPassing 19mm
Product A
100%90%80%70%60%
Product B100%-90%-80%
Product C--100%80%60%
Product D-100%-93%82%
Product E100%--85%-
Now... I know I can use calculation conditions, enter every "Passing ...mm" field as an expression and only show when a particular product is selected. BUT I have 93 products and 52 different "Passing ...mm" sizes so I don't want to have to make 4000+ conditional expressions for each of the 5 charts.
My question then is... Is there a way I can set up the expression based on a rank or something of the Field Name where the value is not blank, or null()? So if I were to look at the table above again in a hypothetical way, it might look like that below. Therefore my first line chart on my dashboard will just show, based on what Product is selected, whichever field is 1. The second chart would show whichever field is 2, etc. etc. This way I'll only need, i presume, 1 expression in each chart.
ProductPassing 63mmPassing 53mmPassing 37.5mmPassing 26mmPassing 19mm

Product A

12345
Product B1-2-3
Product C--123
Product D-1-23
Product E1--2-

I hope this makes sense, if you have any questions or need any further information, please feel free to ask.

Alan

• ###### Re: Ranking only fields that have values

This sounds like a single trellis chart to me.  See attached.  I'm sure I've misunderstood exactly what you want, as the charts I came up with don't make any use of your % numbers (I'm unclear what those mean and where they might go), and show the same tests in every chart, even if the targets are different.  If you can't get what you need from the example, feel free to modify the data to match your actual case - it can be difficult to give a solution without example test data and examples of what you want the charts to look like for that test data.

• ###### Re: Ranking only fields that have values
Hi John,
Perfect answer, thank-you so much for the assistance. I was able to adapt what you provided to what i need.
For those Interested in the adapted result, I have atatched an example. (NB. I have only included test results for 'Product A'
The % values relate to an upper and lower limit for the "Passing ..mm" size. Although i only provided one of the parameters in my initial example, each product actually has an Upper and Lower limit for each "Passing ...mm" size, so Product A for instance, 53mm has a Lower Limit of 81% and Upper Limit of 90%. These are not actually a percentage of anything persay, but the test result of the product for that "Passing ...mm" size must fall within this range.
My only next "issue" will be to load the products table with the "Passing ..m" sizes (or SieveSizes as I'll call them), and the Upper and Lower values for these in a tabular format as you provided in your example, as opposed to the format that it is currently in (as shown in my example above). In other words i need to convert my data;
FROM
Product63mm Upper Limit63mm Lower Limit53mm Upper Limit53mm Lower Limit37.5mm Upper Limit37.5mm Lower Limit
Product A1009190818071
Product B10091--9081
Product C----10081
Product D--10094--
Product E10086----
TO
ProductSieveSizeLowerLimitUpperLimit
Product A63mm91100
Product A53mm8190
Product A37.5mm7180

Product B

63mm91100
Product B37.5mm8190
Product C37.5mm81100
Product D53mm94100
Product E63mm86100
Currently I have the following, which is fine although there are 30 different SieveSizes so it's loading the table a few times this was, and it's a bit harder to maintain and scale. Is there an easier/better way to convert the field names into a value in a column, and populate the value of that field into another column?

Products:

'37.5mm' As SieveSize,
[37.5mm Upper Limit] As UpperLimit,
[37.5mm Lower Limit] As LowerLimit,
Product
FROM
[ProductsTable.qvd]
(qvd)
WHERE [37.5mm Upper Limit] <> null();

Concatenate(Products)
'53mm' As SieveSize,

[53mm Upper Limit] As UpperLimit,
[53mm Lower Limit] As LowerLimit,
Product
FROM
[ProductsTable.qvd]
(qvd)
WHERE [53mm Upper Limit] <> null();

Concatenate(Products)
'63mm' As SieveSize,
[63mm Upper Limit] As UpperLimit,
[63mm Lower Limit] As LowerLimit,
Product
FROM
[ProductsTable.qvd]
(qvd)
WHERE [63mm Upper Limit] <> null();

Thanks,

• ###### Re: Ranking only fields that have values

Probably the most common way to convert columns to rows is with a crosstable load.  Maybe something like this:

Limits:
CROSSTABLE (SieveSize, LowerLimit)
Product
,[63mm Lower Limit] as [63mm]
,[53mm Lower Limit] as [53mm]
,[37.5mm Lower Limit] as [37.5mm]
...
LEFT JOIN (Limits)
CROSSTABLE (SieveSize, UpperLimit)
Product
,[63mm Upper Limit] as [63mm]
,[53mm Upper Limit] as [53mm]
,[37.5mm Upper Limit] as [37.5mm]
...

• ###### Re: Ranking only fields that have values

Thanks again for that response John, that worked perfectly.

Some additional information on this answer to those who may use it, I wasn't able to use LEFT JOIN and CROSSTABLE in the same statement, it gives me an "Illigal combination of prefixes" script error. So you have to load the second CROSSTABLE portion as a temp table first before joining it to the "Limits" table.

Limits:
CROSSTABLE (SieveSize, LowerLimit)
Product
,[63mm Lower Limit] as [63mm]
,[53mm Lower Limit] as [53mm]
,[37.5mm Lower Limit] as [37.5mm]
...

TempLimits:

CROSSTABLE (SieveSize, UpperLimit)

Product

,[63mm Upper Limit] as [63mm]

,[53mm Upper Limit] as [53mm]

,[37.5mm Upper Limit] as [37.5mm]

...

LEFT JOIN (Limits)

*

Resident TempLimits;

Drop Table TempLimits;

I also made a slight variation to your suggestion of the trellis chart above John. This chart is very useful but i found it was a bit hard to view detail as the y-axis scale is fixed. So with, for the same product, some of my limit ranges being 0-3 and others being 92-100 for example, they all just ended up looking like single lines on the chart.

What i have done instead is "Rank" the SieveSize field per product during the load from highest to lowest. Then i have five line charts stacked on-top of each other on screen and are using set analysis within each to give me the results for {<SieveSizeRank={1}>}, {<SieveSizeRank={2}>} ... etc.

Hopefully in future QlikTech might develop variable y-axis scales for the Trellis Charts.