IntervalMatch

[Tal43]

hi 

I would appreciate your assistance

i have table name EmpHier

SalesmanID,
Manager,
Department,
StartDate,
EndDate

i need to get the date between StartDate and EndDate of every Department using the date from master calnder.

how do i start the script ?

[Vegar · Accepted solution]

• Make sure you have loaded your EmpHier table
• Make sure that you have loaded your calendar table.
• Make sure that the fields StartDate,EndDate and Date are all recognised as dates (or numerics).
• Add the following script snippet.

IntervalMatch (Date)

Load  StartDate, EndDate

Resident EmpHier;

You will get a synthetic key, but that's OK. If you don't want it then you can consider joining your intrvalmatch table with EmpHier or Calendar table, OR to create a composite key out of DateStart and DateEnd.

[LRuCelver]

Try the code below. I've assumed SalesmanID to be the key of the EmpHier table. If that is not the case, create a new key field and use it in the code instead.

EmpHierDateLink:
NoConcatenate Load Distinct
	Date
Resident Calendar;

Join Load Distinct
	SalesmanID
Resident EmpHier;

Inner Join IntervalMatch(Date, SalesmanID) Load Distinct
	StartDate,
    EndDate,
    SalesmanID
Resident EmpHier;

Drop Fields StartDate, EndDate From EmpHierDateLink;

[Tal43]

Thank you

[Tal43]

Thank you

[Tal43]

I have a question about the same table

SalesmanID	Department	Manager	StartDate
56700005	Ladies Small Le	70362	01/01/2018
56700005	Management	70362	06/10/2019
56700005	Management	4550	06/10/2019
56700005	Management	4550	06/10/2019

when o load with distinct i got 

SalesmanID	Department	Manager	StartDate
56700005	Ladies Small Le	70362	01/01/2018
56700005	Management	70362	06/10/2019
56700005	Management	4550	06/10/2019

i need that if i have the same Department but the Manager change also not to load this line.

[LRuCelver]

This code keeps the first mentioned Manager for every SalesmanID, Department and StartDate:

Table:
NoConcatenate Load	
	SalesmanID,
    Department,
    FirstValue(Manager) as Manager,
    StartDate
Resident Data
Group By
	SalesmanID,
    Department,
    StartDate;

For your example it results in this:

SalesmanID	Department	Manager	StartDate
56700005	Ladies Small Le	70362	01/01/2018
56700005	Management	70362	06/10/2019

If I change the date of the last row from your example I also keep that row:

SalesmanID	Department	Manager	StartDate
56700005	Ladies Small Le	70362	01/01/2018
56700005	Management	70362	06/10/2019
56700005	Management	4550	06/10/2019
56700005	Management	4550	10/10/2019

becomes

SalesmanID	Department	Manager	StartDate
56700005	Ladies Small Le	70362	01/01/2018
56700005	Management	70362	06/10/2019
56700005	Management	4550	06/10/2019

[Tal43]

what do you mean when you write 

Resident Data ? 

i need to Resident from EmpHier ?

[LRuCelver]

I don't know what your requirements are. If the discarded row is not needed anywhere in the app, I would apply this transformation right at the beginning when creating the EmpHier table.