topic Re: Re : max date based on a field in QlikView

Re : max date based on a field

Tue, 19 May 2015 05:30:17 GMT

Hi all

I have a requirement as follows

my data is regarding the exams done on different courses.

same student can write more than 1 exam.

now i have to take the count of students which has been passed in each exam.

if a student attends more than 1 exam then he should be counted on the latest exam based on the exam date.

how can it be achieved?

Re: Re : max date based on a field

tresB — Tue, 19 May 2015 05:35:06 GMT

Could you post a sample qvw with sample data? It seems, FirstSortedValue() would be useful here.

Re: Re : max date based on a field

Tue, 19 May 2015 05:52:41 GMT

please find the attached.

Re: Re : max date based on a field

tresB — Tue, 19 May 2015 06:08:46 GMT

Is there any possibility that an employee attempted an exam and passed, but still he/she can attempt again for the same exam, and in that case if he/she fails (in the latest attempt) he/she should be considered as FAILED?

Re: Re : max date based on a field

rohit214 — Tue, 19 May 2015 06:09:35 GMT

Hi Reena,

Either you can write script in back end Like

Main:

Load

From XYZ.xls;

Load

Employee Code,

Max(Exam Date) As Exam Date_MAX,

Resident Main group by Employee Code;

you can use expression in front end

=count( {<Employee Code={"$(=FirstsortedValue(Exam Date,Employee Code))"},Exam Result={'Pass'}>}Employee Code)

Re: Re : max date based on a field

Tue, 19 May 2015 06:20:13 GMT

no,the same exam he/she wont be attending again.there is no such chances..

Re: Re : max date based on a field

Tue, 19 May 2015 06:20:53 GMT

Thank yu...

i ll try this.. but how can i get the latest exam based on exam date??

Re: Re : max date based on a field

tresB — Tue, 19 May 2015 06:35:35 GMT

Like attached qvw?

Re: Re : max date based on a field

rohit214 — Tue, 19 May 2015 06:37:15 GMT

same expression

-firstsortedvalue(Exam,Examdate)

Re: Re : max date based on a field

anbu1984 — Tue, 19 May 2015 07:06:45 GMT

=Count(If(Aggr(FirstSortedValue([Exam Result],-[Exam Date]),[Employee Code])='Pass',1))

Re: Re : max date based on a field

Tue, 19 May 2015 09:39:05 GMT

hi..

Thanks for the reply.

Actualy i need the report in following format

->here the first column is the branch code.

->second is Total no of employees

for each xam i should get the count as separate column

here technician,pro technician,diagnosis technician,master technician are Name of exams.

there are possibilities like an employee can write all four exams so his count should be shown only in the latest exam where he is passed.

Re: Re : max date based on a field

Tue, 19 May 2015 11:07:26 GMT

any help on this please?

Re: Re : max date based on a field

Mahamed_Qlik — Tue, 19 May 2015 12:06:32 GMT

Hi Rohit,

We tried at our end but the count is 0 (Zero).

We have checked the source database in which many number of students have passed.

Re: Re : max date based on a field

Wed, 20 May 2015 04:53:19 GMT

helloo..

Any Update??