topic Joining 2 tables in QlikView

Joining 2 tables

Anonymous — Mon, 21 Dec 2015 13:42:32 GMT

Hi,

Qlikview is "not responding" and the excecution never stops after I run this Left Join

can someone explain why this is happening,

Table 1:10M records

Table 2: 27M records

Server has 128 RAM

Please see below code,

Table1:

LOAD AffiliateId,

AdType,

[date(PurchaseDate)] as [Billing Purchase Date],

SaleId,

SalesServicesId

FROM [..\QVD Generator\2_dw\FactBillingTransactions.qvd] (qvd);

LEFT JOIN (Table1)

LOAD SaleId,

JobId

FROM [..\QVD Generator\2_dw\FactSalesServices.qvd] (qvd);

Re: Joining 2 tables

sunny_talwar — Mon, 21 Dec 2015 13:46:22 GMT

If the table you are joining has only two columns, have you tried using Mapping load instead of joining (if each SaleId will only have one JobId)

MappingTable:

Mapping

LOAD SaleId,

JobId

FROM [..\QVD Generator\2_dw\FactSalesServices.qvd] (qvd);

Table1:

LOAD AffiliateId,

AdType,

[date(PurchaseDate)] as [Billing Purchase Date],

SaleId,

SalesServicesId,

ApplyMap('MappingTable', SaleId) as JobId

FROM [..\QVD Generator\2_dw\FactBillingTransactions.qvd] (qvd);

Re: Joining 2 tables

gandalfgray — Mon, 21 Dec 2015 13:48:08 GMT

Syntactically it looks alright.

I suggest you run your load script in debug mode and choose "Limited Load" with a low number to see if you get what you expect.

Re: Joining 2 tables

Anonymous — Mon, 21 Dec 2015 13:55:42 GMT

when limiting to a few milions, the result is OK,

I wonder if it's an overhead for the QV engine...

Re: Joining 2 tables

Anonymous — Mon, 21 Dec 2015 13:57:00 GMT

Sunny, your solution is great - thank you!

I am still intrested to understand why the first way didn't work out for me (in order to master the QV )

Re: Joining 2 tables

sunny_talwar — Mon, 21 Dec 2015 14:02:37 GMT

I don't really know what is going on for your particular solution, but if you look at this blog by HIC you will see that ApplyMap can be faster than join -> Don't join - use Applymap instead