topic Re: Help with a synthetic table in QlikView

Help with a synthetic table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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,

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

Re: Help with a synthetic table

HarishG — Fri, 30 Mar 2018 08:56:17 GMT

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

Re: Help with a synthetic table

nizamsha — Fri, 30 Mar 2018 09:02:36 GMT

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;

Re: Help with a synthetic table

Anonymous — Fri, 30 Mar 2018 09:03:23 GMT

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?

Re: Help with a synthetic table

nizamsha — Fri, 30 Mar 2018 09:22:21 GMT

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

Re: Help with a synthetic table

Anonymous — Fri, 30 Mar 2018 09:35:50 GMT

It helped me breaking the synthetic key, thanks!