topic Re: Comparing Values from the Same Column in QlikView

Comparing Values from the Same Column

Anonymous — Tue, 14 Feb 2017 11:30:03 GMT

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.

Re: Comparing Values from the Same Column

sunny_talwar — Tue, 14 Feb 2017 12:10:36 GMT

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

Re: Comparing Values from the Same Column

Anonymous — Tue, 14 Feb 2017 12:12:30 GMT

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

Re: Comparing Values from the Same Column

sunny_talwar — Tue, 14 Feb 2017 12:20:18 GMT

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;

Re: Comparing Values from the Same Column

Anonymous — Tue, 14 Feb 2017 13:37:28 GMT

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

Re: Comparing Values from the Same Column

sunny_talwar — Tue, 14 Feb 2017 13:41:21 GMT

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;