Previous function issue

[Mahamed_Qlik]

Hi Guys,

I have data as below. My expectation is to create new postiondate wherein in utill it find another date it should keep as it is for each department.

EMP_ID	DEPARTMENT	POSITION_DATE
2001	Accountant	1/1/2007
2001	Finance	8/31/2014
2001	IT	6/7/2018

I have above data, for each employee different department with its changed position date. Here what I am expecting :

EMP_ID	DEPARTMENT	POSITION_DATE
2001	Accountant	1/1/2007
2001	Accountant	1/2/2007
2001	Accountant	1/3/2007
	Upto
2001	Accountant	8/30/2014
2001	Finance	8/31/2014
2002	Finance	9/1/2014
2003	Finance	9/2/2014
	Upto
2001	IT	6/7/2018

For example- I want to create new position date wherein it should cosider every date range until the position/department is changed.

[Dalton_Ruer]

Step 1: You need a Master Calendar that has every single date from the minimum/maximum dates you need to consider. 

Step 2: You need modify your first table to contain both the starting and ending dates for their positions. You cand do that using the Previous or Peek functions. 

Step 3: You need to use the IntervalMatch Function which will create the relationship between the two. 

If you have never use the IntervalMatch functin before I've pasted some sample code that was for a different type of use, but the exact same concept. 

Data:
Load * Inline [
SalesID, Date, SalesAmt
1, 6/27/2020, 1203039
2, 6/28/2020, 1026330
3, 7/27/2020, 1149891
4, 8/27/2020, 9193930
];

IntervalData:
Load * Inline [
IntervalID, StartDate, EndDate
1, 6/1/2020, 6/30/2020
2, 7/1/2020, 7/31/2020
3, 8/1/2020, 11/14/2022
];

Intervals:
IntervalMatch (Date) Load StartDate, EndDate
resident IntervalData;

Join (Intervals)
Load
StartDate,
EndDate,
IntervalID
Resident IntervalData;

drop table IntervalData;

[Mahamed_Qlik]

Hi Dalton,

Thanks for your response.
But, I can see you have created inline table for IntervalsData wherein you have cosider every month start date and month end date.

what If want 10 years of data? do I need to create inline table for every year, everymonth?

[Dalton_Ruer]

My sample code was simply for you to be able to copy/paste into a load script and play with as an example so you can see/learn how the IntervalMatch works. You don't need to hand code the values from your actual tables. Inline is a great way to share example data so that others don't have to try and fake their own values or worry about XLS/CSV files etc.