Data model

[#1]

Dear community,

I am Qlikview newbie and have some trouble with the data model of our CRM-database which is the source for my Qlikview analysis:

In the first table I have 2 columns: ID and text. The other table contains data with a link to the ID. For better understanding I enclosed two pictures:

    

So, from my perspective data model looks like this:

The columns "Birth Year" and "Birth Place" have links to the same column in table 1. How do I set up this in the script (or elsewhere) so that I do not see anymore "1" but "1981"?

Unfortunately data cannot be changed

Thank you very much

[swuehl]

Try a mapping approach:

MAP:

Mapping LOAD ID, Text From Table1;

Table2:

LOAD

     Name,

     Surname,

     applymap('MAP', BirthYear) as BirthYear,

     applymap('MAP', BirthPlace) as BirthPlace

FROM Table2;

[#3 · Accepted solution]

Sebastian,

Swuelh's answer is very OK.

However, I would create 2 dimensions: Birth Year and Birth Place.

Birth_Year:

LOAD ID as ID_Year,

   Field2 as [Birth Year]

FROM xxx

WHERE isnum(Field2);

Birth_Place:

LOAD ID as ID_Place,

   Field2 as [Birth Place]

FROM xxx

WHERE istext(Field2);

Fabrice

[#4]

Thank you both for your quick response. I will check out tomorrow and provide feedback.

[#5]

Both hints worked. Thank you very much.

[#6]

Tag the answer as correct/usefull to close the discussion then

Fabrice