create tables associations with keys from the script editor

[nbuscemi]

Hi,

I need to create tables associations with the primary keys I want from the script editor. The data manager shows in red the associations I propose, so I need to do it custom. 

Thanks!

[Mauritz_SA]

Hi there

What do you mean the data manager shows the proposed associations in red? Can you maybe paste a screenshot? I haven't worked with the data manager in a while, but if I remember correctly in some cases you just need to rename the association in order to make it work.

Regards,

Mauritz

[nbuscemi]

Hi, I do not want to use the data manager to create associations between tables. I want to do that in the script editor. 

[nbuscemi]

I'd like to customize the associations between tables setting the key I want, not the one the data manager chooses.  And I want to do that in the script editor, but I can't figure out a proper way...

[Mauritz_SA]

Okay, the principle is that columns with the same names will automatically be treated as keys when associations are made. If you want Field1 in TableA to be the field that gets associated with Field2 in TableB then you can rename Field1 to Field2 (Load Field1 AS Field2 From TableA) or vice versa (Load Field2 AS Field1 From TableB).

If more than one field is similar in two tables then a synthetic key will be created.

The reason I say this is because it means that in the example above you need to make sure that there isn't another common field (like IsActive) in both tables, otherwise the combination of Field1 and IsActive will be seen as a key.

I am typing this on a phone so it might be a confusing (as I cannot give proper examples), but if you give an example I (or someone in the community) should be able to help you.

[nbuscemi]

That makes sense!

But I'm looking for a way to type in the script editor to manually create these associations (i.e. TableA left join TableB on a specific key I want to indicate myself). I've read about JOIN and KEEP, but that is not enough. I do not want Qlik to suggest/create associations for me. I'm kinda trying to force the creation of these associations typing a SQL statement in the script editor. 

Don't know if I could explain properly...

[happyfeet11]

i am having the same problem as well. I woudl write the script where i can join tqo tables via a primary key and get only necesary fields from both tables and generate a single table .

is it possible?

[Mauritz_SA]

Hi there

Sorry, I missed the reply from @nbuscemi. This is very possible. Send the SQL query and I (or someone on the forum) will be able to help. Where are you pulling the data from? QVDs? If it is from SQL then you can obviously just use your SQL query directly.

Regards,

Mauritz 

[happyfeet11]

the sql query is quite simple actually

select 

t1.name,

t1.lastname,

t2.date,

t2.profession

from table1 t1, table 2  t2

where t2.date >='2019-01-01'

order by date;

[happyfeet11]

i know that the SQL query copy pasted might also work but isnt there any qlikse script for the same