Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
So what happens when the rules are met or are met?? What is the expected output here?
when the rule are met --> 'Pass' otherwise 'Fail' as another column
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;
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
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;