2 table variables for same set of table fields based on one datafield 1

[fcullari]

I have a field limittypesequence and need data from 2 and 4, I was able to figure out the calculation below for 2 separate variables but when I put it in a table I cannot get the results broken down for the same parameter id.

=If IsNull(<Fractionvalue1>)
And <Limittypesequence>=4 Then (<Value1>) Else (<Fractionvalue1>)

=If IsNull(<Fractionvalue1>)
And <Limittypesequence>=2 Then (<Value1>) Else (<Fractionvalue1>)

 

[SkipVought]

would this work?

=If IsNull(<Fractionvalue1>)
 And <Limittypesequence<=2 
 And <Limittypesequence>=4  Then (<Value1>) Else (<Fractionvalue1>)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fcullari]

There are separate data values for limitsequence 2 value 1 or fractionvalue1(Lower Spec Value) and another set for limitsequesnce 4 value 1 or fractionvalue (midpoint), your suggestions would only show 1 value.

 

[SkipVought]

Please explain CLEARLY, the logic for your request.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fcullari]

I put a portion of the table below I need to get data from limittypesequence 2 value1, fractionvalue1, value2, fractionvalue2 and if limittypesequence = 4 value1 and fractionvalue1. If I open the condition to limittypesequence 2 and 4 I get duplicate tests and the columns do not line up. I'm not sure how to get both values in the same table.



Specid Test Value type Limittypesequence Operator 1 Operator2 Value1 Value2 Fraction1 Fraction2
7040 8.8lb@3 L 50% Modulu Standard 2 >= <= 0.71 1.19 NULL NULL
7040 8.8lb@3 L 70% Modulu Standard 2 >= <= 1.31 2.19 NULL NULL
7040 8.8lb@3 L Elongation Standard 2 >= <= 103 127 NULL NULL
7040 8.8lb@3 W Elongation Standard 2 >= <= 55 75 NULL NULL
7040 Flex Reference 2 In NULL PASS NULL NULL NULL
7040 Pattern Repeat Fraction 2 >= <= 3.8125 4.0625 3 13/16 4 1/16
7040 Shade (Pass/Fail) Reference 2 In NULL PASS NULL NULL NULL
7040 Shrinkage Length Standard 2 >= <= 0 5 NULL NULL
7040 Shrinkage Width Standard 2 >= <= 0 5 NULL NULL
7040 Weight (yds/lb) Standard 2 >= <= 9.24 10.22 NULL NULL
7040 Width (Overall) Fraction 2 >= <= 13.75 14.625 13 3/4 14 5/8
7040 8.8lb@3 L 50% Modulu Standard 4 > NULL 1.19 NULL NULL NULL
7040 8.8lb@3 L 70% Modulu Standard 4 > NULL 2.19 NULL NULL NULL
7040 8.8lb@3 L Elongation Standard 4 > NULL 127 NULL NULL NULL
7040 8.8lb@3 W Elongation Standard 4 > NULL 75 NULL NULL NULL
7040 Flex Reference 4 In NULL FAIL NULL NULL NULL
7040 Pattern Repeat Fraction 4 > NULL 4.0625 NULL 3 13/16 NULL
7040 Shade (Pass/Fail) Reference 4 In NULL FAIL NULL NULL NULL
7040 Shrinkage Length Standard 4 > NULL 5 NULL NULL NULL
7040 Shrinkage Width Standard 4 > NULL 5 NULL NULL NULL
7040 Weight (yds/lb) Standard 4 > NULL 10.22 NULL NULL NULL
7040 Width (Overall) Fraction 4 > NULL 14.625 NULL 13 3/4 NULL

BO report is a table needing values for each test with limitsequence4 value1 or fractionvalue1 as the midpoint spec and limitsequence2 value1 or fractionvalue1 as the lower spec and limitsequence2 value2 or fractionvalue2 as the upper spec.

 

[hilfy]

So, if I understand correctly, your table needs to display something like the following columns:

Test
Value type
Operator 1
Operator 2
Upper Spec Value
Upper Spec Fraction
Midpoint Spec Value
Midpoint Spec Fraction
Lower Spec Value
Lower Spec Fraction

Is this correct?

If so, here's what you can do:

For each value/fraction create something like the following formula:

=If([Limittypesequence]=2;[Value2];null)

Then, in the appropriate column on the report put the Max of that formula. By including the Limittypesequence value in the query but not putting it on the report, WebI will aggregate the data up to the fields that are on the report, so the Max of each value or function formula will get you the appropriate value.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.