topic Re: How to get this in QlikView

How to get this

alvinford — Tue, 29 Dec 2015 03:54:41 GMT

Hi All,

Trying to get the following logic. I have the following details in table

i.e. Teacher ID, Teaching Code, Status , Start Date and End Date.

I want to exclude the Teacher ID's who are in Training (teaching Code A16,A17,A18) when the Year or

Month of Teaching period falls in between the Start Date and End Date is selected.

For Example : If Year 2013 and Month 4 is selected, distinct count(Teacher ID) should be 5. i.e. Teacher ID 50440 and 54240 to be excluded from the distinct count of Teacher ID as they are in the training.

Please find the attachment for your reference.

Regards,

Alvin.

Re: How to get this

tamilarasu — Tue, 29 Dec 2015 04:08:04 GMT

Hi Alvin,

Check the below expression

=Count(DISTINCT {<[Teaching Code]-={'A16','A17','A18'}>}[Teacher ID])

Re: How to get this

Tue, 29 Dec 2015 04:32:25 GMT

Hi Alvin,

Checking your data, in year 2013 there are 5 distinct Teacher ID & for the month 4 there are 3 distinct teacher Id & if you remove the In training, then the answer would be 2.

Please check the below expression for the same

=count(DISTINCT(if(Status<>'In Training', [Teacher ID])))

Regards,

Anand B Nagaraj

Re: How to get this

Tue, 29 Dec 2015 06:49:46 GMT

Hi alvinford,

Use Dimension Teacher Id and Teaching Code, and expressions as

1)maxstring({<Status-={'In Training'}>}Status)

2)maxstring({<Status-={'In Training'}>}[Start Date])

3)maxstring({<Status-={'In Training'}>}[End Date])

Suppress Null value for both dimension values.

Please find attachment. This will help you.

Regards,

Devanand

Re: How to get this

Tue, 29 Dec 2015 07:07:51 GMT

Hi,

Sorry, I just misunderstood your Requirement.

Just do :

1)maxstring({<Status-={'In Training', 'End of Training', 'Extension of Training'}>}Status)

2)maxstring({<Status-={'In Training', 'End of Training', 'Extension of Training'}>}[Start Date])

3)maxstring({<Status-={'In Training', 'End of Training', 'Extension of Training'}>}[End Date])

Neglect old attachment.

Re: How to get this

Anonymous — Tue, 29 Dec 2015 07:40:55 GMT

This part is missing in this expression:

when the Year or Month of Teaching period falls in between the Start Date and End Date is selected.

Ford,

Could you share a sample app or let us know how you have derived your calender??

Is it from startdate & enddate?

Re: How to get this

tamilarasu — Tue, 29 Dec 2015 07:50:38 GMT

Not understood the requirement. Let's wait for sample application .

Re: How to get this

jan_gunnars — Tue, 29 Dec 2015 13:33:42 GMT

I think this will do it. It assumes that you will filter on year, month and/or date. See .zip.

I also guess that you mean 4 teachers 10776,10777,10778,10779

Re: How to get this

alvinford — Tue, 29 Dec 2015 14:39:25 GMT

Hi Nagaraj,

Thank You for Your reply.. Would like to achieve it from the Backend.

Regards,

Alvin.

Re: How to get this

jan_gunnars — Tue, 29 Dec 2015 14:45:51 GMT

Have another look and you will se it.

Re: How to get this

alvinford — Tue, 29 Dec 2015 14:48:36 GMT

Hi Jan Gunnars,

Thank you for your reply.

Would like to achieve the logic from backend i.e. from the script.

Any help is greatly appreciated..

Regards,

Alvin.

Re: How to get this

jan_gunnars — Tue, 29 Dec 2015 15:37:11 GMT

Some parts of the code

First add all dates

Teaching_Dates:

Teaching:

//Add intervall when teacher have been teaching
if(match( [Teaching Code], 'A16','A17','A18') > 0,Date([Start Date],'YYYY-MM-DD')) as t_Start_Date,
if(match( [Teaching Code], 'A16','A17','A18') > 0,Date([End Date],'YYYY-MM-DD')) as t_End_Date

Then intervallMatch to add all teaching dates in intervalls

//Create table with teachers and their training dates. If a teacher hasn't been traing he/she is not in table.
Tmp_T:

//Recreate base table without training dates
Tmp_T2:

//Tmp_T and Tmp_T2 is connected with key [Teacher ID]