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;
It works perfect! Thanks so much for sticking with me on this.