Joining the dates

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

Hi,

I have two tables

Table 1:

It consists of EmployeeID and Date of joining of the employee:

LOAD [EmpID] as EmployeeID,

date(date(left(EmpDataofJoining, len(EmpDataofJoining)-9)),'MM/DD/YYYY') as DateofJoining

FROM

Ava_EmployeeMasterList.csv

(txt, unicode, embedded labels, delimiter is '\t', msq);

Table 2:

It consists of EmployeeID and RelievingDate of the employee:

LOAD EmployeeID,

If(Len(Trim(RelievingDate)) = 0, Date(Today(),'MM/DD/YYYY'), Date(RelievingDate,'MM/DD/YYYY')) as RelievingDate

FROM

EPE_LSTRelievingFormInfo.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

The requirement is:

I want to column as Date which consists of dateofjoining as a minimum date and

if the employee is relieved maximum as RelievingDate or

if the employee is not relieved maximum as today

For example I have filter employeeID 138

the employee was joined in the organization on 08/16/2016 and not relieved and working on the organization. so I need a column Date consists of all the dates from 08/16/2016 to 05/29/2017.

I have used the below script

EmployeeInfo:

LOAD [EmpID] as EmployeeID,

date(date(left(EmpDataofJoining, len(EmpDataofJoining)-9)),'MM/DD/YYYY') as DateofJoining

FROM

Ava_EmployeeMasterList1.csv

(txt, unicode, embedded labels, delimiter is '\t', msq);

join

LOAD EmployeeID,

If(Len(Trim(RelievingDate)) = 0, Date(Today(),'MM/DD/YYYY'), Date(RelievingDate,'MM/DD/YYYY')) as RelievingDate

FROM

EPE_Data\EPE_LSTRelievingFormInfo1.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

presentEmp:

LOAD EmployeeID,

DateofJoining as DateofJoining1,

RelievingDate as RelievingDate1

Resident EmployeeInfo;

MinMax:

LOAD Min(DateofJoining1) as Min,

Max(RelievingDate1) as Max

Resident presentEmp;

LET vMin = Peek('Min');

LET vMax = Peek('Max');

DROP Table MinMax;

Calendar:

LOAD Date(Date,'MM/DD/YYYY') as Date ,

Date as MonthYear;

LOAD Date($(vMin) + IterNo() - 1) as Date

AutoGenerate 1

While $(vMin) + IterNo() - 1 <= $(vMax);

Left Join (presentEmp)

IntervalMatch (Date)

LOAD DateofJoining1,

RelievingDate1

Resident presentEmp;

DROP Fields DateofJoining1,RelievingDate1;

But it is not working it is showing as not associated

Anyone can help me!

I have attached my sample data along with this

Regards

Yoganantha Prakash G P

Re: Joining the dates

ahaahaaha — Mon, 29 May 2017 09:25:44 GMT

Hi,

as idea look attached file. In your data where can I see that the employee has not been fired yet, and the previous dates were just his movements?

Regards,

Andrey

Re: Joining the dates

yoganantha321 — Mon, 29 May 2017 09:47:33 GMT

Andrey,

If the employee is not fired(relieving date)

i.e doesn't have relieving date means he is still continuing and the max date is today.

Re: Joining the dates

yoganantha321 — Mon, 29 May 2017 10:29:02 GMT

Andrey,

You have used the data of joining as the same date of relieving it should not be like that

topic Re: Joining the dates in QlikView

Joining the dates

Re: Joining the dates

Re: Joining the dates

Re: Joining the dates