Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Can we use intervalmatch without creating synthetic key??????
If so then plz explain me with example.
Thanks
Use this enclosed file which contains two different example of IntervalMatch...
Please check below script...
DatesTable:
LOAD * INLINE
[
Name, Start, End
First, 01/01/2010, 31/01/2010
Second, 01/02/2010, 28/02/2010
Third, 01/03/2010, 31/03/2010
Four, 01/04/2010, 30/04/2010
];
FactsTable:
LOAD * INLINE
[
ID, Date, Amount
A, 03/02/2010, 100
B, 16/01/2010, 200
C, 15/03/2010, 300
E, 15/06/2010, 600
];
FactsDates:
INTERVALMATCH (Date) LEFT JOIN LOAD Start, End RESIDENT DatesTable;
LEFT Join (FactsTable) LOAD * RESIDENT DatesTable;
DROP TABLE DatesTable;
First of all, the synthetic key that IntervalMatch creates is harmless and will not cause any problems. It is in fact a good solution to this data modelling problem. See IntervalMatch.
However, should you still want to remove it, you can do so by running a second pass (Load resident on a temporary table) and create a composite key from the Start and End fields. I do this quite often when I model Slowly changing dimensions. See more on IntervalMatch and Slowly Changing Dimensions
HIC
Try this code in script and reload it
LOAD * INLINE [
Desc, from-day, to-day
First, 1, 10
Second, 11, 20
Third, 21, 31
];
Emp:
LOAD * INLINE [
Name, day
A1, 1
A1, 5
A1, 2
A2, 7
A2, 15
A3, 25
];
Left join(Emp)
Intervalmatch(day)
LOAD [from-day], [to-day]
RESIDENT Category;
Left join (Emp)
LOAD * resident Category;
drop table Category;
This is the table box which contains above data
I cant able to figure out , whether data is populating correctly....
For ID A , it should show only FIRST and SECOND in name column...
Dear Himanshu,
You should get below result... please check your script again..
Start | End | Name | ID | Date | Amount |
---|---|---|---|---|---|
01/02/2010 | 28/02/2010 | Second | A | 03/02/2010 | 100 |
01/01/2010 | 31/01/2010 | First | B | 16/01/2010 | 200 |
01/03/2010 | 31/03/2010 | Third | C | 15/03/2010 | 300 |
E | 15/06/2010 | 600 | |||
Hi Manish,
I verified many times....I am getting the same table box as I shown you previously by using your script....
Use this enclosed file which contains two different example of IntervalMatch...
Thanks Manish for ur great help...
My pleasure...