COGNOiSe.com - The IBM Cognos Community

Thanks for your responce as said dim table is wrong.



Hello
Can any one help me how to build refernce dimenison from below scenario.

---Table1---
region,Rowid, seqno.

----table2----
rowid, Num1,Num2,town

----IN table 1-----
Highest level --Ã,  Newengland(1 -6) and sequence no is 100
second level-- Virtual(1-4) (seq no 200) and virtual ct(5-6) (seqno 300)
Thrid level
under virtual(1-4) -- Boston(Rowid 1) (seq no 600),Newjeresy(RowidÃ,  2) (seqno 700),NNE(Rowid 3)(seq no 500),SEMA(RowidÃ,  4) (seqno 400)

under virtual ct(5-6)-CT(RowidÃ,  5)(seq no 600), CT2(RowidÃ,  6)(seq no 700)
-----------------------------------------------------

i need to build a dimenison table which hasÃ,  region,rowid,seqno,town,num1,num2. i nee to map rowid in table 1 to row id in table 2.

attached is the sample data for table 1

table 2 is simple records (combination of num1 and num2 is unique)

RowidÃ,  Ã, Num1Ã,  Num2Ã,  Ã, TownÃ, 
1Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 234Ã,  Ã,  Ã, 456Ã,  Ã,  Ã, Waltham
1Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 234Ã,  Ã,  Ã, 457Ã,  Ã,  Ã, watertown
1Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 234Ã,  Ã,  Ã, 576Ã,  Ã,  Ã, newton
2Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 732Ã,  Ã,  Ã, 667Ã,  Ã,  Ã, edison
2Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 732Ã,  Ã,  Ã, 668Ã,  Ã,  Ã, metro
..

I feel there is something missing in table1. Because there is no way to classify the levels properly. if we take the rptseq it makes a hierarachy for region. But the levels you wanted would not be possible..

srik