The Crazy Resigners

[Orsi68]

Hello, I have a problem like this: I have a table of resigners whose fields are
student ID, surname, name, date of resignation and job code. My aim is to create a table in which, to these fields, I have to add another field called status in which the serial number can become ceased or terminated based on the selection date in qlik which I call competence date. In practice, if the resignation date is greater than the accrual date selected, the enrollment must have ceased. If the resignation date is less than or equal to the accrual date, the enrollment must have ceased. I need these data because I have to create, and this is the real objective, a dynamic table in which I have, for each job code, the count of the number of freshmen who are ceased or terminated. thanks for whoever can help me.

[jcmachado · Accepted solution]

You can accomplish this by using the following steps:

1. Create a new field in your table called "status" and set its initial value to "active".

2. Use an if statement to check if the resignation date is greater than the selected competence date. If it is, update the status field to "ceased". If not, update the status field to "terminated".

3. Use the "Group by" and "Count" functions to group the data by job code and count the number of students with each status (ceased or terminated).

Here's an example of the code you can use to accomplish this:

// Step 1: Create a new field called "status" and set its initial value to "active" LOAD student_id, surname, name, resignation_date, job_code, 'active' as status;

// Step 2: Use an if statement to check the resignation date // and update the status field accordingly if(resignation_date > competence_date, 'ceased', 'terminated') as status;

// Step 3: Use the Group by and Count functions to group the data // by job code and count the number of students with each status GROUP BY job_code, status; COUNT(student_id) as count_students;

Please note that this code is just a sample and you may need to create a new field in the table of resigners called "status" and use a formula or calculation to determine if the student's enrollment should be considered "ceased" or "terminated" based on the comparison of the resignation date and the selected accrual date in Qlik. Then, you would need to use this "status" field to create a dynamic table that shows the count of students who are "ceased" or "terminated" for each job code.

[Orsi68 · Accepted solution]

ok, thanks, I will try to implement your suggestions to the code that I had already developed. I hope to improve all the code. many thanks for your interest. bye