topic need help in expression in QlikView

need help in expression

Thu, 19 Jun 2014 06:44:14 GMT

Hi,

please help me in below scenario how to write expression:

courseid={1,2,3,4,5,6} --> count(courseid)=6

but if courseid matches with courseid {1} count should be +2 means--> count(courseid)=8

courseid {2} count should be +3 courses --> count(courseid)=9

if {1},{2} both matches count should be count(courseid)=6+5=11

Regards

Damodhar

Re: need help in expression

tresB — Thu, 19 Jun 2014 06:48:24 GMT

What do you mean by 'if courseid matches..' ? Is it 'if selected ' ?

Re: need help in expression

rwunderlich — Thu, 19 Jun 2014 07:04:19 GMT

count(courseid) + if(max(match(courseid, '1'))>0,2) + if(max(match(courseid, '2'))>0,3)

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: need help in expression

Thu, 19 Jun 2014 07:14:05 GMT

if {1} exists in courseid field ..

courseid {1} had 2 subcourses : so if {1} exists in courseid field i should increase my count +2.

Re: need help in expression

tresB — Thu, 19 Jun 2014 07:21:08 GMT

In that case Rob's solution would work. Have you tried?

Re: need help in expression

Thu, 19 Jun 2014 08:59:49 GMT

Hi Rob,

Your calculation is working fine as individual .

but same thing when i tried in following expression its not working .

in the following condition how should i implement your logic :

Condition:1)CURRICULUM_TITLE = 'Core Analyst and Associate Director'

2) courseid 's should match with these two fields H_COURSE_ID,COURSE_ID

can you look into below expression:

aggr( count ( {<CURRICULUM_TITLE = {'Core Analyst and Associate Director'} >}

DISTINCT if ( Match (H_COURSE_ID,COURSE_ID) =1 , COURSE_ID+ if(max(match(COURSE_ID, 'cours000000000001643'))>0,7) + if(max(match(COURSE_ID, 'cours000000000001644'))>0,6) )) ,SABA_EMPLOYEE_ID)

Thanks

Damodhar

Re: need help in expression

rwunderlich — Thu, 19 Jun 2014 15:13:04 GMT

The additional increments belong outside the count, not inside it.

=aggr(

count ( {<CURRICULUM_TITLE = {'Core Analyst and Associate Director'} >}

DISTINCT

if ( Match (H_COURSE_ID,COURSE_ID) =1

,COURSE_ID

)

+ if(max(match(COURSE_ID, 'cours000000000001643'))>0,7) + if(max(match(COURSE_ID, 'cours000000000001644'))>0,6) )

,SABA_EMPLOYEE_ID)

I don't quite understand what you are doing with the If() inside the count, but that filter would probably be better in the Set Analysis.

Please mark the initial answer correct if it answers your original question.

-Rob