topic Re: Create a new count table for a table with intervals in QlikView

Create a new count table for a table with intervals

madushanfernand — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

following is the data set which i extracted from the data base ( Query 1 and Table 1 ).

can anyone please help me to get the out put as follows ( Table 2 ).

Here is my case :

Table 1 has the employee id and his or her career start date and and career end date.

i want to create a table ( Table 2 ) to get the active employee count for the year and month.

for an instance, if the year is 2018 and month is JAN, then the active employees as of 31/01/2018 count should be calculated as

COUNT (DISTINCT IF (EMP_START_DATE<=MonthEndDate and MonthEndDate<EMP_END_DATE, EMP_ID))

where MonthEndDate is 31/01/2018 for the above example.

Can we create the solution table using SQL or Qlikview.? Any of the way is expected.

Your help would be really appreciated.

Thank you

Madushan

Query 1,

Table 1,

Table 2,

mdmukramali — Mon, 30 Jul 2018 06:53:11 GMT

Hi,

You can follow the Mr. hic‌ below post which will help you to full fill your requirement.

Kindly find the attached application which I have created a sample document based on employee HireDate and EndDate.

if you have any further help do let me know.

Thanks,

Mohammed Mukram.

madushanfernand — Mon, 30 Jul 2018 07:23:52 GMT

Hi Mukram

thanks a lot for your response.

i have gone through the post. but couldn't find which you have attached.

could you please send me the attachment again.

mdmukramali — Mon, 30 Jul 2018 08:28:29 GMT

Hi,

find the Attached the file.

madushanfernand — Mon, 30 Jul 2018 11:36:21 GMT

thank you very much for your assistance, this is what exactly i wanted.