topic Re: Need help in expression in QlikView

Need help in expression

Thu, 14 Nov 2013 14:09:30 GMT

Hi All,

I have the following 3 fields I need the count of Student Id's who have completed the course

CourseID         StudentId Marks
    100                 1               95
     101                2               65
     101                1               35
     100                2
     102                1               36

I want to see how many have completed each course ? Completed means who has marks

expected o/p should be based on the course Id
CourseId Completed Count
100 1

101 2

102 1

PS:

Tried the following expression,it didnt work out

=count({$ <course_id={00100},score-={''}>}(ID)) (For a single course)

Is there any other way apart from Set Analysis as an work around? Do let me know

Thanks in advance

Re: Need help in expression

Aurelien_Martinez — Thu, 14 Nov 2013 14:17:14 GMT

Hi,

Try this expression

Count( If(alt(Marks), StudentId))

Regards,

Aurélien

Re: Need help in expression

jerem1234 — Thu, 14 Nov 2013 14:19:32 GMT

Try a straight chart with CourseID as dimension and this expression:

count({<Marks-={''}>}StudentID)

Gave me same output as you have listed.

Hope this helps!

Re: Need help in expression

salto — Thu, 14 Nov 2013 14:24:54 GMT

Hi,

try this expression in a chart with the CourseID as a dimension:

=count( { < Marks -={''}>} Student)

It will give you a yntax error, but it is QV bug. It works for me.

HTH.

Re: Need help in expression

Anonymous — Fri, 15 Nov 2013 14:22:01 GMT

Hi,

You can try with script like this:

set NullInterpret ='';

TAB1:

LOAD * Inline

[CourseID, StudentId, Marks

100, 1, 95

101, 2, 65

101, 1, 35

100, 2,

102, 1, 36];

TAB2:

LOAD

CourseID,

StudentId,

Marks,

If(IsNull(Marks), 0, 1) as Bit

Resident

TAB1;

TAB3:

LOAD CourseID,

Sum(Bit) as Count,

Count(StudentId) as C

Resident

TAB2

Group By

CourseID;

DROP Tables TAB1, TAB2;

Hope this will work for you.

Regards,

Harshal Gawande