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

Two tables with the same column name in my data model

Thu, 08 Jan 2015 14:19:32 GMT

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

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

rubenmarin — Thu, 08 Jan 2015 14:23:16 GMT

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

Anonymous — Thu, 08 Jan 2015 14:23:50 GMT

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

Surname as [Client Surname] ,

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

Thu, 08 Jan 2015 14:29:24 GMT

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

Anonymous — Thu, 08 Jan 2015 15:32:01 GMT

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

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

agomes1971 — Thu, 08 Jan 2015 15:37:35 GMT

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

rubenmarin — Thu, 08 Jan 2015 16:13:18 GMT

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.

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

Tue, 20 Jan 2015 11:07:35 GMT

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

avinashelite — Tue, 20 Jan 2015 11:18:41 GMT

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,

from table1;

output:

table1.A

table.B

eg2:

Qualify *;

Unqualify A;

table2:

LOAD A,

from table2;

output:

table2.B

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

jmmayoral3 — Tue, 20 Jan 2015 11:25:51 GMT

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

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

Tue, 20 Jan 2015 11:51:37 GMT

Hi presh,

LOAD

Primary_contact,
Surname,

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