topic Data model in QlikView

Data model

Sat, 07 Dec 2013 10:58:43 GMT

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

Re: Data model

swuehl — Sat, 07 Dec 2013 11:04:45 GMT

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;

Re: Data model

Sat, 07 Dec 2013 13:52:35 GMT

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

Re: Data model

Sat, 07 Dec 2013 16:56:12 GMT

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

Re: Data model

Sun, 08 Dec 2013 14:56:22 GMT

Both hints worked. Thank you very much.

Re: Data model

Sun, 08 Dec 2013 15:31:11 GMT

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

Fabrice