topic Re: Join between two fact tables in QlikView

Join between two fact tables

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have two fact tables with a common field: "LeadsUniqueNum"

The fact1 table contains M21 records and 20 columns

The fact2 table contains M10 records and 6 columns

I would like to perform a left join between the fact1 table and the fact2 table based on the common field, and add all the columns in the fact2 table to the fact1 table,

but the performance is very slow.

Is there a faster way?

Following for example is the fact2 table:

Lead_MobilePhone	Lead_MainPhone	Lead_Center	Lead_Category	Leads_Status	LeadsUniqueNum
7896558821	5878995254	aa	x	Active	123
788662055	8796558897	bb	y	Not active	456
48745885	8796552258	cc	x	Not active	789

Example of the script:

Fact1:
load LeadsUniqueNum,* FROM

(qvd);

LEFT join(Fact1)
Fact2:
LOAD LeadsUniqueNum,
Leads_Status,
Lead_Category,
Lead_Center,
Lead_MainPhone,
Lead_MobilePhone
FROM (QVD);

Thanks in advance,

Re: Join between two fact tables

sunny_talwar — Mon, 05 Dec 2016 11:07:36 GMT

You can look into creating multiple mapping loads:

Don't join - use Applymap instead

Re: Join between two fact tables

Mon, 05 Dec 2016 13:28:39 GMT

I tried using multiple mapping loads but the performance is much more slower

Re: Join between two fact tables

sunny_talwar — Mon, 05 Dec 2016 13:47:00 GMT

Hmmm really, that is strange because I have found Mapping Loads to be faster then joins. Not sure what other options you might have.

Re: Join between two fact tables

oknotsen — Mon, 05 Dec 2016 13:50:38 GMT

I would try this:

Load both tables into memory first, so not doing the join directly while loading the QVD.

Next, do something like this:

LEFT join(Fact1)
Fact2temp:
LOAD *

RESIDENT Fact2

;

drop table Fact2;

Might need a "noconcatenate" to work.

Re: Join between two fact tables

Mon, 05 Dec 2016 14:08:39 GMT

One minute savings from three and a half minute loading

Thx Onno!

Re: Join between two fact tables

muthukumar77 — Mon, 05 Dec 2016 17:37:36 GMT

Hi,

You can ApplyMap or Don't do left join (I mean Self join).

Note: If your going to use Apply map, Fact1 table should be Mapping table.