Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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% | - |
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
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.
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.
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 |
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,
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]
...
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.