Extended Interval Match

[Smeenakshi23]

Hello

I am having a calendar table linked with the fact table,

Calendar :

Left keep ( fact)

dt_key,// linked with fact

date

from calendar.

Department Table:

load Automnumbet startdate enddate as key 1 / not correct syntax.. I just typed

CD // Linked with fact,

Class // Linked with fact

start date,

end date,

performance

 from department table

I am performing interval match.

interval table ;

interval  match( date)

load start date,

end date resident department;

temp :

load  date,Automnumbet startdate enddate as key 1

  resident  Interval table:

When executing this i am getting circular reference loop. On removing the link bewtwee the department table and fact table i am not getting performance filed selected,

So i tried implementing like this

interval match(date, performance)

laod startdate, endate,performance

resident department;

temp :

load  date,Automnumbet startdate enddate as key 1

  resident  Interval table:

is the above this correct

[Vegar · Accepted solution]

Try something like this:

SET DateFormat='YYYY-MM-DD';

Dept:
LOAD * , AutoNumberHash128( StartDate, EndDate, CA, Class ) AS %Dept  inline[ 
StartDate, EndDate, CA, Class, Performance
2019-01-01, 2019-11-30, A, 1, Good
2019-12-01, 2019-12-31, A, 1, Bad
2020-01-01, 2020-01-31, A, 1, Bad
2020-02-01, 2020-02-03, A, 1, Good
2019-01-01, 2019-11-30, B, 1, Bad
2019-12-01, 2019-12-03, B, 1, Average
2020-01-01, 2020-01-31, B, 1, Good
2020-01-01, 2020-02-03, B, 1, Good
];

Fact:
LOAD 
	Date, 
	AutoNumberHash128(Date, CA, Class) as %Fact,
	CA, Class, Dimension 
inline [
Date,  CA, Class, Dimension
2019-10-30, A,1, ABC
2019-12-30, A,1, BCD
2020-01-30, A,1, CDE
2019-02-02, A,1, DEF
2019-10-30, B,1, DEF
2019-12-30, B,1, CDE
2020-01-30, B,1, BCD
2019-02-02, B,1, ABC
2020-01-06, A,1, CDE
2019-12-02, A,1, DEF
2019-01-30, B,1, DEF
2019-02-30, B,1, CDE

];

Link:
IntervalMatch(Date, CA, Class)
LOAD 
	StartDate, 
	EndDate, 
	CA, 
	Class 
Resident Dept;

LEFT join 
LOAD 
	AutoNumberHash128( StartDate, EndDate, CA, Class ) as %Dept,
	AutoNumberHash128(Date, CA, Class) as %Fact
Resident 
	Link;
Drop field CA, Class, Date, StartDate, EndDate From Link;
Drop field CA, Class From Fact;


Calendar:
LOAD 
MonthName(Date) as YearMonth,
dayname(Date) as Date;
LOAD 
today()-365+RecNo() as Date
AutoGenerate 365
Where exists (Date, today()-365+RecNo()) // Just to limit the amuount of days in my example
;

It will give you this datamodel:

See attached qvw

[Smeenakshi23 · Accepted solution]

Thanks a lot for your example

[Vegar]

I'm not sure I completely understand, but can't you just perform the necessary joins first and then apply this logic at the end of your script?