Set analysis question

[#1]

sum({<YCode= {$(=Max([Year Code]))},PCode={'YEAR'},[XM]={'NC'}>} ECount)

To this set analysis condition I want to add one more condition, where I have to make sure if Field1=Field2

sum({<Field1={Field2},YCode= {$(=Max([Year Code]))}, PCode={'YEAR'},[XM]={'NC'}>}ECount)

That doesn’t work at all. How do you handle these kind of conditions in Set Analysis where neither constants or variables are involved. I tried to write with the if condition, but that also doesn’t work.

[jonathandienst]

Hi

You cannot do row by row comparisons using set analysis. Set analysis is applied once to the full data set. You have two options:

• Use Sum({<..>} If()) This can perform poorly in a large data set, although comparing two fields in the same table is fairly quick unless the data set is huge.

• Create a flag field during load. Use something like:

          LOAD

               ...                   

               If(Field1 = Field2, 1, 0) As FieldEqualFlag,

               ...

         Then use FieldEqualFlag = {1} in your set expression.

HTH

Jonathan

[sundarakumar]

If u want to enable the condition YCode= {$(=Max([Year Code]) only if field1=field2 thenu can achive it it in expression insteead of set analysis like, in expression

if(field1=field2,

    sum({<YCode= {$(=Max([Year Code]))},PCode={'YEAR'},[XM]={'NC'}>} ECount),

          sum({<PCode={'YEAR'},[XM]={'NC'}>} ECount) )

     This is from what i have understood on ur req. if u need something else pls specify.

-sundar

[#4]

Can you try:

sum({<Field1={"=$(Field2)"},YCode= {$(=Max([Year Code]))}, PCode={'YEAR'},[XM]={'NC'}>}ECount)

or

if(Field1=Field2,sum({<YCode= {$(=Max([Year Code]))}, PCode={'YEAR'},[XM]={'NC'}>}ECount))

Are your Field1 and Field2 as dates?

Thanks

[PrashantSangle · Accepted solution]

Hi,

You can use it by indirect set analysis

Try this,

sum({<Field1=p(Field2),YCode= {$(=Max([Year Code]))}, PCode={'YEAR'},[XM]={'NC'}>}ECount)

Regards,

[#6]

thanks max

[PrashantSangle]

Hi,

You Welcome..

Regards,