Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Join between two fact tables

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,

6 Replies
sunny_talwar

You can look into creating multiple mapping loads:

Don't join - use Applymap instead

Not applicable
Author

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

sunny_talwar

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

oknotsen
Master III
Master III

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.

May you live in interesting times!
Not applicable
Author

One minute savings from three and a half minute loading

Thx Onno!

muthukumar77
Partner
Partner

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.

Muthukumar Pandiyan