qlik freezing up during the reload with left join

[cristianferraraed]

Ciao, sto cercando di fare un join sinistro di una tabella. Ma quando provo a ricaricare il tempo si blocca e non va avanti. ho provato a caricarlo senza join sinistro e va tutto bene. Come posso risolvere questo problema con il join sinistro? sto usando qlikview desktop la vecchia versione

[marcus_sommer]

It's probably caused by any issue with the join-keys and your system is running out of RAM. Joining millions records against millions records is a heavy computation and could result in many billions of records if anything is wrong.

Therefore take a look on the key-fields if it's the right ones and properly spelled and if this looks fine you may reduce the number of records of the join sources maybe with a first 1000 statement or any where-clauses to finish the join-load and then to look for any issues with the data-quality within your key-fields.

[cristianferraraed]

i do this:

LOAD ContrattoLegacyID as Pratica,
     ContrattoID,
     AziendaID,
     AgenteID,
     SubAgenteID,
     StatoContrattoID,
     MacroProdottoID,
     ProdottoID,
     ConvenzionatoID,
     TabellaFinanziariaID,
     BeneID,
     DealerID as Dealer_ID,  //In seguito al cambiamento del riferimento sulla tab dealer, rinomino la chiave per non dover modificare tutti gli oggetti
     AutoMarcaID,
     AutoModelloID,
     FilialeID,   //Filiale ID associato al contratto
     CaptiveID,
     EmettitoreID,
     ClienteID,
     VenditoreID,
CaptiveID as EmettitoreVenduto,
     DataCaricamento,
     DataDecorrenza,
     year(DataDecorrenza)&Num(Month(DataDecorrenza),'00') as AnnoMeseDecorrenza,
     year(DataCaricamento)&Num(Month(DataCaricamento),'00') as AnnoMeseCaricamento,
     CodiceRisorsaCommerciale,
     ImportoFinanziato as Finanziato,
     NumeroRate,
     ImportoRata,
     ImportoRataFinale,
     ImportoBene,
     ImportoNettoErogato,
     DataRiscatto,
     ImportoRiscatto,
     ImportoCommissione,
     Telaio,
     Targa,
     ValoreCapitale,
     ImportoAnticipato,
     ImportoInteressi,
     ImportoSpeseIstruttoria,
     PercentualeTassoReale,
     PercentualeTan,
     PercentualeTasso,
     DescrizioneBene,
     Rating,
     PercentualeTaeg,
     ContributoCasaMadre as ContributoATerzi,
     ContributoDealer,
     ContributoDilazione,
     ValoreEurotax,
     Kilometri,
     DataImmatricolazioneID,
     DataUltimaScadenza,
     DataPrimaScadenza,
     CategoriaProdotto // aggiunta per SALES-17498
FROM
[..\..\..\..\REPOSITORY\Dati\Contratti.qvd]
//[E:\Produzione\REPOSITORY\Dati\Contratti.qvd]
(qvd)
where year(DataCaricamento)&Num(Month(DataCaricamento),'00') >= '$(vAnnoMeseRif)' and (MacroProdottoID = 'CO' OR MacroProdottoID = 'CQ' OR MacroProdottoID = 'LE');// Year(Today())&Num(Month(Today()),'00');

//where year(DataCaricamento)&Num(Month(DataCaricamento),'00') >= 201401 and (MacroProdottoID = 'CO' OR MacroProdottoID = 'CQ' OR MacroProdottoID = 'LE');// Year(Today())&Num(Month(Today()),'00');
//where year(DataCaricamento)&Num(Month(DataCaricamento),'00') >=  199001 and (MacroProdottoID = 'CO' OR MacroProdottoID = 'CQ' OR MacroProdottoID = 'LE');// Year(Today())&Num(Month(Today()),'00');
left join
LOAD
    EmettitoreID,
    Marca,
    Modello,
    Allestimento
FROM [..\..\..\..\REPOSITORY\Dati\Durables.qvd]
(qvd)
WHERE Marca = 'SCOTT' OR Marca = 'SPECIALIZED';

also if is 182.000 fetched going in freeze. 

[marcus_sommer · Accepted solution]

In regard to the specified join-key it looked ok. but if the relationship between tables isn't 1:1 or n:1 there may a lot of record-duplication be created.

Beside this the tables contain millions of records and quite a lot of columns and therefore the above hinted lack of resources may lead to the noticed freeze. This could be very simply monitored by looking on the task-manager during the reload. How looks the resource-consumption of the system before the reload and then what and when changes it while the load runs.

Beside the above suggestion to reduce temporary the number of records you could also just copy & paste these two join loads into an empty application and running it there. It would avoid the RAM consumption from previous loads of your origin application and also excluding some unintended dependencies - for example you didn't specified the table on which the join should be performed which means the join is applied to the previous table which may not the wanted one if they aren't in a direct order.