topic Re: Creating a link between two .QVD in QlikView

Creating a link between two .QVD

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

Hi All,

I have two QVDs that need to link together so I can create various charts, tables, etc on the back off this match.

The Opportunity.qvd should contain the date field [Opportunity_StageAudit.Date], which is contained in the Opp_stage.qvd.

How do I do this in the script?

All help much appreciated

Re: Creating a link between two .QVD

Miguel_Angel_Baeyens — Thu, 27 Jul 2017 15:59:43 GMT

If the rest of the fields in each QVD have a different name, do a

Stage:

LOAD *

FROM Opp_stage.qvw (qvd);

Opp:

LOAD *

FROM Opportunity.qvd (qvd);

And that will load both tables so you can start with the charts and analysis

Re: Creating a link between two .QVD

krishnacbe — Thu, 27 Jul 2017 16:00:18 GMT

Hi,

If the Date Format is same in two QVDs and [Opportunity_StageAudit.Date] is the only Key field in both QVDs you can directly load two QVDs. Qlik will join two tables with common field.

If you are looking for more details, share the Sample Data.

Re: Creating a link between two .QVD

knightwriter — Thu, 27 Jul 2017 16:12:00 GMT

Hi Miguel,

Thanks for the response. Will that do the join between the two tables? As below, the other calendars are linked to the Opportunity table and I am looking to join the table on the right (Stage_Calendar) to the Opportunity table via the date field [Opportunity_StageAudit.Date]. Thanks

Re: Creating a link between two .QVD

knightwriter — Thu, 27 Jul 2017 16:15:28 GMT

Thats the issue, the [Opportunity_StageAudit.Date] is one QVD and not the other. Im looking to create a join between the two.

There is infact an Opp_ID filed that is common to both tables - could I join this way via the script?

Re: Creating a link between two .QVD

krishnacbe — Thu, 27 Jul 2017 16:23:11 GMT

[Stage Change Date] and [Opportunity_StageAudit.Date] have same format and if you wish to join using these two dates you can just use [Stage Change Date] as [Opportunity_StageAudit.Date] while loading the second table.

If the Opp_ID exist in two tables automatically join will be done, if name is different you need to RENAME any one field in any one table.

For Example:

Stage:

LOAD *,

[Stage Change Date] as [Opportunity_StageAudit.Date]

FROM Opp_stage.qvw (qvd);

Opp:

LOAD *

FROM Opportunity.qvd (qvd);

Re: Creating a link between two .QVD

knightwriter — Thu, 27 Jul 2017 16:44:32 GMT

[Opportunity_StageAudit.Date] is in the Stage_Calendar table and Opportunity table.

How do I join the [Opportunity_StageAudit.Date] to the Opportunity table to create the join?

Re: Creating a link between two .QVD

krishnacbe — Thu, 27 Jul 2017 17:03:22 GMT

Hope you have qualified the Opportunity Table. Unqualify the [Opportunity_StageAudit.Date] in the Opprotunity Table to do the automatic join.

Wrote below statement after the Opportunity table load.

UNQUALIFY [Opportunity_StageAudit.Date];

Re: Creating a link between two .QVD

ziadm — Thu, 27 Jul 2017 20:25:50 GMT

Canonical Date

Re: Creating a link between two .QVD

Miguel_Angel_Baeyens — Fri, 28 Jul 2017 09:22:17 GMT

You need to use the JOIN LOAD in order to "merge" two tables, very similar to what you would do with SQL. Loading the files with the same field will associate them, so unless you need to join both tables in a single, bigger one, the results should be correct