topic Re: Ranking only fields that have values in QlikView

Ranking only fields that have values

Mon, 22 Oct 2012 21:53:25 GMT

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)

Product	Passing 63mm	Passing 53mm	Passing 37.5mm	Passing 26mm	Passing 19mm
Product A	100%	90%	80%	70%	60%
Product B	100%	-	90%	-	80%
Product C	-	-	100%	80%	60%
Product D	-	100%	-	93%	82%
Product E	100%	-	-	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.

Product	Passing 63mm	Passing 53mm	Passing 37.5mm	Passing 26mm	Passing 19mm
Product A	1	2	3	4	5
Product B	1	-	2	-	3
Product C	-	-	1	2	3
Product D	-	1	-	2	3
Product E	1	-	-	2	-

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

Thanks in advance.

Alan

Re: Ranking only fields that have values

johnw — Tue, 23 Oct 2012 00:00:45 GMT

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

Tue, 23 Oct 2012 02:25:02 GMT

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

Product	63mm Upper Limit	63mm Lower Limit	53mm Upper Limit	53mm Lower Limit	37.5mm Upper Limit	37.5mm Lower Limit
Product A	100	91	90	81	80	71
Product B	100	91	-	-	90	81
Product C	-	-	-	-	100	81
Product D	-	-	100	94	-	-
Product E	100	86	-	-	-	-

Product	SieveSize	LowerLimit	UpperLimit
Product A	63mm	91	100
Product A	53mm	81	90
Product A	37.5mm	71	80
Product B	63mm	91	100
Product B	37.5mm	81	90
Product C	37.5mm	81	100
Product D	53mm	94	100
Product E	63mm	86	100

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:

LOAD
'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)
LOAD
'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)
LOAD
'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

johnw — Tue, 23 Oct 2012 16:12:37 GMT

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

Limits:
CROSSTABLE (SieveSize, LowerLimit)
LOAD
Product
,[63mm Lower Limit] as [63mm]
,[53mm Lower Limit] as [53mm]
,[37.5mm Lower Limit] as [37.5mm]
...
LEFT JOIN (Limits)
CROSSTABLE (SieveSize, UpperLimit)
LOAD
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

Tue, 23 Oct 2012 23:21:51 GMT

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)
LOAD
Product
,[63mm Lower Limit] as [63mm]
,[53mm Lower Limit] as [53mm]
,[37.5mm Lower Limit] as [37.5mm]
...

TempLimits:

CROSSTABLE (SieveSize, UpperLimit)

LOAD

Product

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

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

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

...

LEFT JOIN (Limits)

LOAD
*

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.