Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have tried looking at all the discussions on this site but still can't get what I want! I keep getting results different that what I exopect and with a sythnetic table. Seems basic all I want is ONE table with the following results...
Entity | ServiceDate | Ownership_Pct | Effective_YearMth | Expiration_YearMth |
DEAN | 200901 | 0.2 | 200801 | 999912 |
DEAN | 200701 | 0.1 | 200701 | 200712 |
DNON | 201001 | 0.6 | 200901 | 999912 |
LOAD * INLINE [
Entity, Effective_YearMth, Expiration_YearMth, Ownership_Pct
DEAN, 200701, 200712, 0.10
DEAN, 200801, 999912, 0.20
DNON, 200001, 200812, 0.50
DNON, 200901, 999912, 0.60
];
FACT:
Load * Inline [
Entity, ServiceDate
DEAN, 200901
DEAN, 200701
DNON, 201001];
Left Join(FACT)
IntervalMatch([ServiceDate],Entity)
Load
Effective_YearMth,
Expiration_YearMth,
Entity
Resident OWNER_DEF;
Yes. Change the last Join to
Right Join (OWNER_DEF)
Load * Resident FACT;
I believe, In your requirement, IntervelMatch won't fix the problem because both tables having Entity.
Please try code like below:
TEMP:
LOAD * INLINE [
Entity, Effective_YearMth, Expiration_YearMth, Ownership_Pct
DEAN, 200701, 200712, 0.10
DEAN, 200801, 999912, 0.20
DNON, 200001, 200812, 0.50
DNON, 200901, 999912, 0.60
];
Left Join
Load * Inline [
Entity, ServiceDate
DEAN, 200901
DEAN, 200701
DNON, 201001];
FINAL:
LOAD * Where FLAG;
LOAD * , IF(ServiceDate >= Effective_YearMth AND ServiceDate <= Expiration_YearMth , 1,0) AS FLAG
Resident TEMP;
DROP Table TEMP;
DROP Field FLAG ;
I would not join the tables - but you can if you know that there is a one-to-one relationship between the dates and the intervals. I would instead go for the following model:
HIC
Thanks for the response. Unfortunately I am not sure this will work. In my FACT table (from above) there is a chance there will be a record that does not join to the Owner Def table so it will get dropped because it is on the right side of the left join.
Henric -
Thanks for the response. Is there any way to just get all the data into the fact table. and not have to deal with a bridge table. we have quite a large model and I am hoping just to bring all the data into the FACT table.
Steve
No problem. Check attached script.
HIC
Thanks again.
Getting close. However the resulting table is the Owner Def table. I would lose my FACT table - which is the main table in our model. The Owner Def table is just an attributes table that can/should go away after using it in the interval match..
Any thoughts?
Steve
Just change the Join to FACT table like below:
OWNER_DEF:
LOAD * INLINE [
Entity, Effective_YearMth, Expiration_YearMth, Ownership_Pct
DEAN, 200701, 200712, 0.10
DEAN, 200801, 999912, 0.20
DNON, 200001, 200812, 0.50
DNON, 200901, 999912, 0.60
];
FACT:
Load * Inline [
Entity, ServiceDate
DEAN, 200901
DEAN, 200701
DNON, 201001];
Join (FACT)
IntervalMatch([ServiceDate],Entity)
Load
Effective_YearMth,
Expiration_YearMth,
Entity
Resident OWNER_DEF;
Left Join (FACT) // Left Join Keep only all records in FACT Tabel
Load * Resident OWNER_DEF;
Drop Table OWNER_DEF;
No, the table is the join between the tables, so it contains all information from both tables. Yes, it is labeled OWNER_DEF, but this is just a matter of labeling. To change the join order - so that you start with the joining to the FACT table - does not change anything.
HIC
Okay I think I have it finally! My last question is that an extra row is created in the resulting table (bolded record with DNON and no service date or area). This record was not from the FACT table but got created through the join process. Any way to only have the FACT table records records?
The resulting table is (updated the code below slighlty). :
Entity | ServiceDate | Ownership_Pct | Effective_YearMth | Expiration_YearMth | Area |
DEAN | 200901 | 0.2 | 200801 | 999912 | North |
DEAN | 200701 | 0.1 | 200701 | 200712 | South |
DNON | 201001 | 0.6 | 200901 | 999912 | East |
DNON | 0.5 | 200001 | 200812 | ||
TEST | 201001 | West |
LOAD * INLINE [
Entity, Effective_YearMth, Expiration_YearMth, Ownership_Pct
DEAN, 200701, 200712, 0.10
DEAN, 200801, 999912, 0.20
DNON, 200001, 200812, 0.50
DNON, 200901, 999912, 0.60
];
FACT:
Load * Inline [
Entity, ServiceDate, Area
DEAN, 200901, North
DEAN, 200701, South
DNON, 201001, East
TEST, 201001, West];
Join (OWNER_DEF)
IntervalMatch([ServiceDate],Entity)
Load
Effective_YearMth,
Expiration_YearMth,
Entity
Resident OWNER_DEF;
Join (OWNER_DEF)
Load * Resident FACT;
Drop Table FACT;