Lookup Tables in Star Schema.....

[#1]

Hi All,

I am wondering how one would create a lookup table with a number as the primary Key....

Let me try to explain -

I have 3 tables,

      Names,     

     Location,

     Fact Table

they are all joined by ID

ID	F_Name	L_Name
10	Charlie	Brown
20	Lucy	Smith
30	Linus	Rogers
40	Sally	Brown
50	Peppermint	Patty

ID	City	State
12	Seattle	WA
21	New York	NY
31	Chicago	IL
41	Los Angeles	CA
51	Tuscan	AZ

ID	F_Name	L_Name	City	State
100	Charlie	Brown	Chicago	IL
101	Linus	Rogers	Seattle	WA
102	Sally	Brown	New York	NY
103	Peppermint	Patty	Chicago	IL
104	Lucy	Smith	Los Angeles	CA

Now, Suppose I didnt want the abbreviation for State - I wanted it spelled out.  Normally, I would create a look up table and do an ApplyMap function to do that.  In this case, because the State field is going off of the ID field (Number)  I am not able to do that.

Surely someone has run into this problem - Any suggestions?  Files are attached to illustrate what I am talking about.

thanks

_L

[nagarjuna_kotha]

mean while can you share ur expected output

-Nagarjun

[rupamjyotidas]

What is teh issue here , applymap should work

All i Can see your data, ID is different, which will make it Hard to get the details for Each ID.Then you must use the String field to link all

[nagarjuna_kotha]

Here you can ccheck the data, I hope this is what your looking for .

FactState:

Mapping LOAD State,

    [ST Name]

FROM

(ooxml, embedded labels, table is Sheet1);

LocationState:

Mapping LOAD State,

    [ST Name]

FROM

(ooxml, embedded labels, table is Sheet2);

Names:

LOAD ID,

    F_Name,

    L_Name

FROM

(ooxml, embedded labels, table is Names);

Location:

LOAD ID,

    City,

    State,

    ApplyMap('LocationState',State) as StateName

FROM

(ooxml, embedded labels, table is Location);

FactTable:

LOAD ID,

    F_Name as FName,

    L_Name as LName,

    City as FTCity,

    State as FTState,

    ApplyMap('FactState',State) as FTStateName

FROM

(ooxml, embedded labels, table is FactTable);

For your ref i have sample statenames how to use those for applymap()

Check it out.

I hope this may helps you.

-Nagarjun