Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
mihai_iov
Contributor II

Help with a synthetic table

I'm having 4 different tables:
macro_area(primary key is "id")

micro_area(primary key is "id"; foreign key is macro_area_id referencing macro_area(id))

user(primary key is "id"; foreign key is macro_area_id referencing macro_area(id))
and finally
user_micro_area(primary key is "id" , foreign keys are: micro_area_id referencing micro_area(id), macro_area_id referencing macro_area(id) and user_id referencing user(id))

The user_micro_area table is establishing the relation between micro_area table and the user table(this is how I received the db)

This is the loading script:

LOAD id as micro_area_id,

    version as micro_area_version,

    codice as micro_area_codice,

    descrizione as micro_area_descrizione,

    `macro_area_id`;

SQL SELECT *

FROM alfaintes.`micro_area`;

LOAD id as user_micro_area_id,

    version as user_micro_area_version,

    `macro_area_id`,

    `micro_area_id`,

    `user_id`;

SQL SELECT *

FROM alfaintes.`user_micro_area`;

LOAD codice as macro_area_codice,

    id as macro_area_id;

SQL SELECT codice,

    id

FROM alfaintes.`macro_area`;

LOAD cognome as Cognome,

    id as user_id,

    nome as Nome,

    macro_area_id,

    username,

    enabled,

    ruolo_id as role;

SQL SELECT cognome,

    id,

    nome,

    macro_area_id,

    username,

    enabled,

    ruolo_id

FROM alfaintes.`user`;

When I run the loading script, it generates a synthetic table that has more records than it should be. How should I tackle this issue?
I have attached a screenshot of the tables' relation

1 Solution

Accepted Solutions
nizamsha
Valued Contributor II

Re: Help with a synthetic table

Might be try this

LOAD id as micro_area_id,

     version as micro_area_version,

     codice as micro_area_codice,

     descrizione as micro_area_descrizione,

//macro_area_id`;

LOAD id as user_micro_area_id,

          version as user_micro_area_version,

          //macro_area_id`,

          micro_area_id`,

          user_id`;

 

LOAD cognome as Cognome,

     id as user_id,

     nome as Nome,

     //macro_area_id,

     username,

     enabled,

     ruolo_id as role;

5 Replies
Partner
Partner

Re: Help with a synthetic table

Why don't you join the 3 tables on primary key, this could avoid forming a synthetic key?

nizamsha
Valued Contributor II

Re: Help with a synthetic table

Might be try this

LOAD id as micro_area_id,

     version as micro_area_version,

     codice as micro_area_codice,

     descrizione as micro_area_descrizione,

//macro_area_id`;

LOAD id as user_micro_area_id,

          version as user_micro_area_version,

          //macro_area_id`,

          micro_area_id`,

          user_id`;

 

LOAD cognome as Cognome,

     id as user_id,

     nome as Nome,

     //macro_area_id,

     username,

     enabled,

     ruolo_id as role;

mihai_iov
Contributor II

Re: Help with a synthetic table

I'm having a little trouble with the Joins in QlikView, could you guide me step by step or provide a script in the loading script?

nizamsha
Valued Contributor II

Re: Help with a synthetic table

I think if u use my  code that i gave it wont create synthetic key .if you are facing any problems let me know 

mihai_iov
Contributor II

Re: Help with a synthetic table

It helped me breaking the synthetic key, thanks!