topic Re: Joins overwrite column with the same field in QlikView

Joins overwrite column with the same field

Wed, 01 Jun 2011 08:40:21 GMT

Two tables, the load/select statements as follows:

LOAD event_id, data_id AS data_amne_id;
SQL SELECT * FROM `innan_portning`.`bokning_amnen`;
OUTER Join LOAD id AS data_amne_id, namn AS namn_amne; 
SQL SELECT * FROM `innan_portning`.`data_amnen`;

The Key that should combine the two tables are the "data_amne_id"-field.

The two tables contain the following:

bokning_amnen:

event_id data_amne_id

200 1

201 3

202 10

203 2

........................

data_amnen:

data_amne_id namn_amne

1 Math

2 Phys

3 Biologi

4 Chemi

........................

What I would like is a table like this:

bokning_amnen:

event_id namn_amne

200 Math

201 Biologi

202 Tech

203 Phys

........................

But at the moment all the fields are overwritten with the first textstring, for example "Math".

Would love some help!

Re: Joins overwrite column with the same field

Wed, 01 Jun 2011 08:54:27 GMT

I have tested ur data ...

its working perfectly in Mine.

Re: Joins overwrite column with the same field

Wed, 01 Jun 2011 09:01:49 GMT

I'm afraid I cannot test your QW-file since im trying it out with an Personal edition.

My table becomes:

bokning_amnen:

data_amne_id event_id namn_amne

1 200 Math

1 201 Math

1 202 Math

1 203 Math

Yours doesn't look like this?

thanks.

Re: Joins overwrite column with the same field

Wed, 01 Jun 2011 09:15:43 GMT

Im afraid I was wrong.

The sort-order got changed and since my table contained alot more entries in a similar order I didn't realize this.

Thanks for confirming it, made me look closer! 🙂

cheers

Re: Joins overwrite column with the same field

Miguel_Angel_Baeyens — Wed, 01 Jun 2011 09:18:02 GMT

Hello,

The associative logic will display proper results with no need of joining. So in your model above, should you load both tables and select value "201" in field event_id you will get "BIologi" in field namn_amne.

Anyway, if you require to get only one file, try the following:

IdNameMap:
MAPPING LOAD data_amne_id,
      namn_name;
SQL SELECT * FROM `innan_portning`.`data_amnen`;
TableWithNames:
LOAD event_id, 
     ApplyMap('IdNameMap', data_id, 'No correspondence') AS namn_name;
SQL SELECT * FROM `innan_portning`.`bokning_amnen`;

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Joins overwrite column with the same field

Wed, 01 Jun 2011 09:20:36 GMT

A follow-up:

If I would like to remove the previous Key (data_amne_id), since i joined the tables and therefor this field isn't really needed. Im trying to use the following but it doesn't remove the field:

INNER JOIN First 100 LOAD id AS data_amne_id, namn AS namn_amne;

DROP Field data_amne_id;

SELECT * FROM data_amnen;

Any ideas?

Re: Joins overwrite column with the same field

Miguel_Angel_Baeyens — Wed, 01 Jun 2011 09:44:45 GMT

Hi,

What about the mapping table above? Does that suit you?

Regards

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Joins overwrite column with the same field

Wed, 01 Jun 2011 10:03:57 GMT

Woops, wrote my reply meanwhile you posted yours so missed it

I haven't reached the mapping-sections in the manual yet so am abit unsure how it works. But if I copy-paste it to my script it shows me the error "Field not found <data_amne_id>" (?).

b.r.

Re: Joins overwrite column with the same field

Miguel_Angel_Baeyens — Wed, 01 Jun 2011 10:19:21 GMT

Hi,

Take the following script as an example:

CodesNamesMap: // I'm using INLINE load, data can come from any other source
MAPPING LOAD * INLINE [
Code, Name
1, Maths
2, Philosophy
3, Bio
];
IdCodeTable:
LOAD Id, ApplyMap('CodesNamesMap', Code, 'No Name') AS Name INLINE [
Id, Code
200, 1
201, 1
202, 3
203, 8
];

According to the above, it should return the following output

200, Maths

201, Maths

202, Bio

203, No Name

Check the attached file just in case.

Hope that helps

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica