Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Thanks a lot for your example
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?