Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Tal43
Contributor II
Contributor II

IntervalMatch

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 ?

Labels (2)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

  • 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.

View solution in original post

8 Replies
Vegar
MVP
MVP

  • 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
Partner - Creator III
Partner - Creator III

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
Contributor II
Contributor II
Author

Thank you

Tal43
Contributor II
Contributor II
Author

Thank you

Tal43
Contributor II
Contributor II
Author

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
Partner - Creator III
Partner - Creator III

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
Contributor II
Contributor II
Author

what do you mean when you write 

Resident Data ? 

i need to Resident from EmpHier ?

LRuCelver
Partner - Creator III
Partner - Creator III

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.