topic Re: Interval Match help in QlikView

Interval Match help

Wed, 25 Nov 2015 16:26:17 GMT

Dear Friends,

I appreciate your time. Could you kindly have a look at the below code or the attached application and please let me know the error in the interval match function.

Employee:

Load * inline [

EmployeeName, id, Designation, Employmentdate

Kelvin,1,Eng,04/09/08

Kelvin,1,Sr.Eng,04/09/09

];

Employees:

Load

EmployeeName,

id,

Designation,

Date(Employmentdate,'DD/MM/YYYY') AS Employmentdates

Resident Employee;

drop table Employee;

Salary:

Load * inline [

id,Salary,Startdate

1,20000,01/01/08

1,40000,01/01/09

];

Salary_1:

Load

id,

Salary,

Startdate,

if(Previous(id)= id,date(Date#(Startdate)-1)) as EndDate

Resident Salary

Order by Startdate desc;

drop table Salary;

Salary_2:

Load

id,

Salary,

Date(Startdate,'DD/MM/YYYY') AS Startdates,

Date(EndDate,'DD/MM/YYYY') AS EndDates

resident Salary_1;

drop table Salary_1;

IntervalMatch(Employmentdates)

Load Startdates,EndDates

Resident Salary_2;

Thanks so much!

Kiruthi

Re: Interval Match help

swuehl — Wed, 25 Nov 2015 16:33:28 GMT

Maybe like this

Employee:

Load * inline [

EmployeeName, id, Designation, Employmentdate

Kelvin,1,Eng,04/09/08

Kelvin,1,Sr.Eng,04/09/09

];

Employees:

Load

EmployeeName,

id,

Designation,

Date(Employmentdate,'DD/MM/YYYY') AS Employmentdates

Resident Employee;

drop table Employee;

Salary:

Load * inline [

id,Salary,Startdate

1,20000,01/01/08

1,40000,01/01/09

];

Salary_1:

Load

id,

Salary,

Startdate,

if(Previous(id)= id,date(Date#(previous(Startdate))-1), today()) as EndDate

Resident Salary

Order by Startdate desc;

drop table Salary;

Salary_2:

Load

id,

Salary,

Date(Startdate,'DD/MM/YYYY') AS Startdates,

Date(EndDate,'DD/MM/YYYY') AS EndDates

resident Salary_1;

drop table Salary_1;

left join(Employees)

IntervalMatch(Employmentdates, id)

load Startdates,EndDates, id

Resident Salary_2;

Re: Interval Match help

Wed, 25 Nov 2015 16:51:17 GMT

Thank you!!