topic Re: How to force a JOIN in script in QlikView

How to create a link between two tables in script

cliff_clayman — Wed, 25 Nov 2020 16:16:04 GMT

I have 3 tables that share a field, but not all the values are the same. I want to JOIN two of the tables to a table that has unique values only in it. I am looking to do this so that when I create a table or a list box, I can use the field from the unique table so that when a user clicks on the name, the proper data is returned. I've attached a copy of the QVW that I am working with. I have qualified each name on the tables because I do not want a JOIN between the Slip and Backlog tables. I just want to JOIN Slip and Backlog to ProjectManager on the field PM Name.

Re: How to force a JOIN in script

Peter_Cammaert — Mon, 27 Feb 2017 17:23:03 GMT

Do you really need a 3rd table with all possible values but each of them only once?

Just rename them so that all fields have the exact same name. A QlikView field (in a listbox) will only display unique values, whether you have 10000 identical copies or not.

Re: How to force a JOIN in script

cliff_clayman — Mon, 27 Feb 2017 17:26:49 GMT

Yes, the issue is that when I create a straight table with additional data and click on the PM Name field, it does not limit the data correctly in other charts/tables because I am only using the field from one particular table.

Re: How to force a JOIN in script

aarkay29 — Mon, 27 Feb 2017 17:26:56 GMT

may be this

LOAD

[PM Name]

FROM

[..\PM Names.xlsx](ooxml, embedded labels, table is Backlog);

concatenate

LOAD

[PM Name]

FROM

[..\PM Names.xlsx] (ooxml, embedded labels, table is Slip)

where

Not Exist([PM Name]);

concatenate

LOAD

[PM Name]

FROM

[..\PM Names.xlsx] (ooxml, embedded labels, table is ProjectManager)

where Not Exist

([PM Name]);

Re: How to force a JOIN in script

cliff_clayman — Mon, 27 Feb 2017 17:32:01 GMT

I'm not looking to make one table. I am trying to solve for an issue where I have additional data in the Backlog and Slip tables. I then create straight tables of data and click on the PM Name field in one of those straight tables. The other straight tables that has a different PM Name from the other table does not get filtered.

Re: How to force a JOIN in script

aarkay29 — Mon, 27 Feb 2017 17:38:34 GMT

If you are linking the tables with the key PM Name then when you select a PM Name only the respective PM Name will be filtered in the other tables as well.

There must be a different link key to link different PM names in different tables.

Re: How to force a JOIN in script

cliff_clayman — Mon, 27 Feb 2017 17:39:53 GMT

Right, that is why I wanted to create the third table with the unique values only to be able to select from that instead of the PM Name from each table. I added additional data to the QVW to help illustrate the issue.

Re: How to create a link between two tables in script

albertovarela — Mon, 27 Feb 2017 17:58:55 GMT

Check this Technical brief. You may find it useful...

Concatenate vs Link Table

Re: How to force a JOIN in script

aarkay29 — Mon, 27 Feb 2017 19:00:06 GMT

I see there is no link between the tables, there is no common field name as you were Qualifying PMName in every table.

Use this instead.

ProjectManager:

LOAD [PM Name]

FROM

[..\PM Names.xlsx]

(ooxml, embedded labels, table is ProjectManager);

QUALIFY *;

UnQualify PMName;

Backlog:

LOAD *,PMName as PMNameBlog

FROM

[..\PM Names.xlsx]

(ooxml, embedded labels, table is Backlog);

Slip:

LOAD *,PMName as PMNameSlip

FROM

[..\PM Names.xlsx]

(ooxml, embedded labels, table is Slip);

Re: How to create a link between two tables in script

cliff_clayman — Mon, 27 Feb 2017 19:10:47 GMT

Yes, that does it. Thank you!

Re: How to create a link between two tables in script

cliff_clayman — Mon, 27 Feb 2017 19:14:32 GMT

I have a related question to this issue. I manually created the "linked" table called ProjectManager. Is there a way to create that table in the script from the data in the other two tables? The values in the table are just the unique PMNames from the other two tables.

Re: How to force a JOIN in script

aarkay29 — Mon, 27 Feb 2017 19:24:40 GMT

I did not understand exactly

Do you want to link the tables using a link table which has all the unique PMNames??

Re: How to create a link between two tables in script

cliff_clayman — Mon, 27 Feb 2017 19:25:36 GMT

Yes, but I originally manually created that table myself in Excel. I want to find a way to create it in the script instead.

Re: How to create a link between two tables in script

aarkay29 — Mon, 27 Feb 2017 19:32:10 GMT

[Link Table]:

LOAD

[PM Name]

FROM

[..\PM Names.xlsx](ooxml, embedded labels, table is Backlog);

concatenate ([Link Table])

LOAD

[PM Name]

FROM

[..\PM Names.xlsx] (ooxml, embedded labels, table is Slip)

where

Not Exist([PM Name]);

concatenate ([Link Table])

LOAD

[PM Name]

FROM

[..\PM Names.xlsx] (ooxml, embedded labels, table is ProjectManager)

where Not Exist

([PM Name]);

This will be your table with all the unique PMNames, Then load each table and link them with [PMName]

Re: How to create a link between two tables in script

cliff_clayman — Mon, 27 Feb 2017 19:40:26 GMT

I had to modify it a bit, but I got it to work...thanks!!