Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

rafael5958
Not applicable

synthetic tables error

Hi all,

I have few tables but in qlikview Im using two:

Table AG:

AG.ID      PK

ATEND.ID   FK

Name

Number

Table ACCOUNT

ACCOUNT.ID  PK

AG.ID       FK

ATEND.ID    FK

Name

Number

Adress, etc

They are both connected on qlikview by AG.ID and ATEND.ID, it generates a synthetic table with AG.ID and ATEND.ID.

When I do a research it shows me rows that dont exists, for example, I have same ATEND.ID in both tables, but AG.ID only in AG,

so it creates in  ACCOUNT a row with AG.ID and ATEND.ID and the other fields blanked (not null, that "-" signal).

What am I doing wrong ?

1 Solution

Accepted Solutions
er_mohit
Not applicable

Re: synthetic tables error

i think to remove synthetic and null then try to use join function i.e left join on the basis of that table where your Table AG has complete data fields if not then try to use outer join in qlikview write simply join

AG:

load (AG.ID & '|' & ATEND.ID) as Key,

AG.ID,  //    PK

ATEND.ID , // FK

Name,

Number

from AG;

join

ACCOUNT:

load (AG.ID & '|' & ATEND.ID) as Key,

ACCOUNT.ID, // PK

AG.ID    ,  // FK

ATEND.ID , //  FK

Name,

Number,

Adress,

from ACCOUNT;

hope it helps

5 Replies
Not applicable

Re: synthetic tables error

will you post one test app.

Not applicable

Re: synthetic tables error

may i know what is pk and fk

Not applicable

Re: synthetic tables error

hi

try to use only one field common between two table. so in qlikview both table automatically linked.

write code like this

Table AG:

load

AG.ID ,   

ATEND.ID,  

Name,

Number

from table;

Table ACCOUNT:

load

ACCOUNT.ID ,

AG.ID  as  AG.ID_new,

ATEND.ID ,

Name as Name_new

Number  as  Number_new

Adress

from table;

er_mohit
Not applicable

Re: synthetic tables error

i think to remove synthetic and null then try to use join function i.e left join on the basis of that table where your Table AG has complete data fields if not then try to use outer join in qlikview write simply join

AG:

load (AG.ID & '|' & ATEND.ID) as Key,

AG.ID,  //    PK

ATEND.ID , // FK

Name,

Number

from AG;

join

ACCOUNT:

load (AG.ID & '|' & ATEND.ID) as Key,

ACCOUNT.ID, // PK

AG.ID    ,  // FK

ATEND.ID , //  FK

Name,

Number,

Adress,

from ACCOUNT;

hope it helps

rafael5958
Not applicable

Re: synthetic tables error

Thank you all, I think the Mohit Sahrma way was the better one.