Getting Interval Match wrong.

[Anonymous]

Hi All,

I have a table say tab1 with empid, Date and some other fields. The date has every date from the joining date of the employee till today. Like if the joining date is 1/1/2017 and leave column which has 0 by default.

empid Date     On leave

1      1/1/2017 0

1      2/1/2017 0

.

.

.

1       25/4/2017 say today.

.Now I have another table say tab2 which contains the details of the employee leaves

Like

empid leave_start_date Leave_end_date

1         05/02/2017   10/02/2017

1         06/03/2017    10/03/2017

.

.

.

Now i need to update the Onleave column to 1 if the Date falls in  the interval of leaves.

IntervalMatch (Date,TmpSPID)

Load [leave_start_date ],

     [Leave_end_date],[empid] as TmpSPID

Resident tab2;

But it doesnt give me any records even i have leaves for the employee in the leaves table.

Am not sure what am doing wrong. Hope some1 can guide me.

[Anil_Babu_Samineni]

Look here IntervalMatch

[Anonymous]

Anil, I have gone through the link already. As I need the interval of respective employees, I tried as stated in Slowly Changing Dimensions. Please correct me if i am wrong

[Anil_Babu_Samineni]

You can use WildMatch() for respective employees follow the link which i provide earlier

[Anonymous]

Anil, I didnt get you. Can you please explain.

[Anonymous]

stalwar1‌hic‌

[sunny_talwar · Accepted solution]

Where is TmpSPID coming from? I think it is important to have the id name same between the two table before you do interval match because the synthetic key needs to include the ID as one of the fields...

Tab1:

LOAD TmpSPID,

    Date,

    OnLeave

FROM ....;

Tab2:

LOAD empid as TmpSPID,

    leave_start_date,

    Leave_end_date

FROM ....;

IntervalMatch(Date, TmpSPID)

LOAD leave_start_date,

    Leave_end_date,

    TmpSPID

Resident Tab2;

[Anonymous]

Yes sunny, Just now got it correct. Should have seen that clearly. Thank you very much.