Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Lookup Tables in Star Schema.....

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

 

IDF_NameL_Name
10CharlieBrown
20LucySmith
30LinusRogers
40SallyBrown
50PeppermintPatty

 

IDCityState
12SeattleWA
21New YorkNY
31ChicagoIL
41Los AngelesCA
51Tuscan

AZ

 

IDF_NameL_NameCityState
100CharlieBrownChicagoIL
101LinusRogersSeattleWA
102SallyBrownNew YorkNY
103PeppermintPattyChicagoIL
104LucySmithLos AngelesCA

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

3 Replies
nagarjuna_kotha
Partner
Partner

mean while can you share ur expected output

-Nagarjun

rupamjyotidas
Specialist
Specialist

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
Partner
Partner

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