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.

Not applicable

Two tables with the same column name in my data model

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


10 Replies

Re: Two tables with the same column name in my data model

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

Re: Two tables with the same column name in my data model

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

     Surname     as     [Client Surname] ,

Not applicable

Re: Two tables with the same column name in my data model

Hi Ruben,

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

Re: Two tables with the same column name in my data model

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
Valued Contributor II

Re: Two tables with the same column name in my data model

Hi,

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

André Gomes

Re: Two tables with the same column name in my data model

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.

Not applicable

Re: Two tables with the same column name in my data model

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

Re: Two tables with the same column name in my data model

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
Contributor

Re: Two tables with the same column name in my data model

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);