topic Re: Set analysis question in QlikView

Set analysis question

Wed, 19 Mar 2014 05:17:59 GMT

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.

Re: Set analysis question

jonathandienst — Wed, 19 Mar 2014 05:29:36 GMT

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

Re: Set analysis question

sundarakumar — Wed, 19 Mar 2014 05:32:38 GMT

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

Re: Set analysis question

Wed, 19 Mar 2014 05:33:23 GMT

Can you try:

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

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

Are your Field1 and Field2 as dates?

Thanks

Re: Set analysis question

PrashantSangle — Wed, 19 Mar 2014 05:45:21 GMT

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,

Re: Set analysis question

Wed, 19 Mar 2014 05:50:52 GMT

thanks max

Re: Set analysis question

PrashantSangle — Wed, 19 Mar 2014 05:53:51 GMT

Hi,

You Welcome..

Regards,