Comparing Values from the Same Column

[Anonymous]

Hi All,

Wondering if you guys could help with my query.

I have some data in 3 columns and I must run some rules on them to the compare the values. However, all the values that must be compared lie in the same column. See data below. I need to do this in load script.

Data:

RRef              CRef          Value

R30               C20               5

R80               C20               3

R90               C20               2

R30               C30               8

R80               C30               5

R90               C30               3

........

The rule is:

1) value of R30&C20 = value of R80&C20 + value of R90&C20

2) value of R30&C30 = value of R80&C30 + value of R90&C30

and so on

Any help would be appreciated.

[sunny_talwar]

So what happens when the rules are met or are met?? What is the expected output here?

[Anonymous]

when the rule are met --> 'Pass' otherwise 'Fail' as another column

[sunny_talwar]

Try this:

Table:

LOAD * INLINE [

    RRef, CRef, Value

    R30, C20, 5

    R80, C20, 3

    R90, C20, 2

    R30, C30, 8

    R80, C30, 5

    R90, C30, 3

];

Left Join (Table)

LOAD CRef,

  If(Sum(If(RRef = 'R30', Value)) = Sum(If(Match(RRef, 'R80', 'R90'), Value)), 'Pass', 'Fail') as Flag

Resident Table

Group By CRef;

[Anonymous]

Thank Sunny,

This is very close to what i need.

However, in the data there are other RRef (R10 to R200) that do not relate to this test. Using the left join also gives me a fail for the other RRef that the test does not relate to.

Example Output of you script:

Table:

    RRef, CRef, Value, Flag

    R30, C20, 5               Pass

    R80, C20, 3               Pass

    R90, C20, 2               Pass

    R10, C20, 10             Pass

    R20, C20, 10             Pass

......

Required Output:

Table:

    RRef, CRef, Value, Flag

    R30, C20, 5               Pass

    R80, C20, 3               Pass

    R90, C20, 2               Pass

    R10, C20, 10             N/A

    R10, C20, 10             N/A

[sunny_talwar]

May be do a finaltable load

FinalTable:

NoConcatenate

LOAD  RRef,

    CRef,

    Value,

    If(Match(RRef, 'R30', 'R80', 'R90'), Flag, 'N/A') as Flag

Resident Table;

DROP Table Table;