Two tables with the same column name in my data model

[#1]

Hi all,

I have the script below, the problem is both tables (client and contact) have a column called "Surname". The script returns contact.Surname; however, I am interesting in client.Surname. IT is unable to rename the column(s). Can I add a prefix to "surname" to specify that I want client's surname? or what is the best way to approach this problem?

Test:

LOAD

Primary_contact,
Surname,

SQL SELECT *
FROM client
left join contact

on client_ID = contact_ID

Regards

[rubenmarin]

Hi presh, I think your best option is espcify wich fields you want in the SQL query:

Test:

LOAD

Primary_contact,
Surname,

SQL SELECT Primary_contact, client.Surname
FROM client  left join contact on client_ID = contact_ID

This way you avoid the SQL query to query values that are not used

[Anonymous]

Rename it in QlikView like this with your preceding load statement :

     Surname     as     [Client Surname] ,

[#4]

Hi Ruben,

I would have gone with this option but I am looking at selecting close to 50 columns in actual fact...

[Anonymous]

You could try putting this before the load, which will prefix all the output field names with the table name.

     QUALIFY *;

And this after

     UNQUALIFY *;

[agomes1971]

Hi,

first thing: always qualify your data in first place never forget... QlikView Data Association Model...

André Gomes

[rubenmarin]

Hi, if you don't want to name each field you can try as you said, this doesn't work?:

LOAD Primary_contact,
client.Surname as Surname,

SQL SELECT *
FROM client
left join contact

on client_ID = contact_ID

I usually name all fields in select, no matter how many there are, so never tried this.

[#8]

Hi all,

Please excuse the late response - I have been off for some days.

Please can you briefly explain 'qualify' and 'unqualify' and the reason for them?

Regards

[avinashelite]

Hi

QUALIFY * will rename all the fields with their table names.feildname

UNQUALIFY A :this will not rename the field with their table name

eg:

QUALIFY *;

table1:

load A,

      B

from table1;

output:

table1.A

table.B

eg2:

Qualify *;

Unqualify A;

table2:

LOAD A,

B

from table2;

output:

A

table2.B

[jmmayoral3]

You can try to divide data load in two parts:

Client:

LOAD

client_ID as id_join,

Primary_contact,

Surname;

SQL SELECT *

FROM client;

left join

LOAD

contact_ID as id_join,

*;

SQL SELECT *

FROM contact

WHERE EXISTS (id_join, contact_ID);