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 think you might be leaving something out of your description. The field Performance shouldn't affect your data modelling as you are describing it, why do you want to include it as a parameter to your interval match?
I tried to follow your description of your data model using pen and paper and found the reason to your loop to be the original intervalmatch table, named IntervalMatch in my picture. Try to drop it and you should be fine.
sorry if my post wasnt clear.
The field CD and class from departement table are linked with the fact table.. so i renamed both the field inorder to remove the connection.
So wen i remove the connection between dea[rtment table and fact table the performance field from department table are not populating data properly
e==getting loop error even dropping it
Can you post a screenshot of your data modell? It might be easier to understand?
I don't know your data, but normally you would need to have a connection between department and transactions, preferably you have some kind of department ID that you can connect to the department dimension.
Through which tables are your loop? Is it through the transactions table? On which fields and table? Could you explain a bit more?
If you see fact table there is a ditted link
I suggest you get rid of the direct connection between Department and your transactions. Pass through your Class and CA fields through the interval match instead, if they identifies your relationship between the two.
I am sorry.. I dint get it. can u please share me an example