Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Joins overwrite column with the same field

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!

1 Solution

Accepted Solutions
Not applicable
Author

I have tested ur data ...

its working perfectly in Mine.

View solution in original post

8 Replies
Not applicable
Author

I have tested ur data ...

its working perfectly in Mine.

Not applicable
Author

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.

Not applicable
Author

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

Miguel_Angel_Baeyens

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

Not applicable
Author

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?

Miguel_Angel_Baeyens

Hi,

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

Regards

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Not applicable
Author

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.

Miguel_Angel_Baeyens

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