Joining 2 tables

[Anonymous]

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);

[sunny_talwar]

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);

[gandalfgray]

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.

[Anonymous]

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

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

[Anonymous]

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 )

[sunny_talwar]

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