Problem LeftJoin resident Million of rows - Qlik Community

Anonymous · ‎2016-10-26

Hello,

i'm trying to reload my qvw using sql server and qvd

so on my script i'm using (left join) many times, based on a principal table (sales) then left join to other resident tables.

the loading is doing okay, but in the last (left join) crush ( memory is going to 256go) then he stop

i'm using millions of rows

in général my qvw looks like 10go

i tried to move the table in witch he crushed to load it as first and it's going okay! ( there are no problems with the script but i think that's related to optimasation.

would you please take a look in the screenshot

any help please

Ce message a été modifié par : mohamed ali cherif

Gysbert_Wassenaar · ‎2016-10-26

You're right. The left join is eating all your machines memory. Can you post the script you're trying to execute?

talk is cheap, supply exceeds demand

Anonymous · ‎2016-10-26

Hello Gysbert,

FYI: the memory go up to 256Go (max of the Server Ram) on the last left join before :

STORE VenteMpx into 'E:\QVD\QVD\VenteMpx.qvd';

Drop Table VenteMpx;

Please Find below the script

Regards,

the script:

--------------

OCGeant:

LOAD [oc1] ,[DATE DEBUT]

,[DATE FIN] ;

SQL SELECT distinct [OC] as oc1

,[DATE DEBUT]

,[DATE FIN]

FROM [dbo].[OC Geant]

WHERE [Code Article] IS NOT NULL ;

OCMPX:

LOAD [OC] ,DEBUT

,FIN ;

SQL SELECT distinct [OC]

,[DATE DEBUT] AS [DEBUT]

,[DATE FIN] AS [FIN]

FROM [dbo].[OC MPX]

WHERE [Code Article] IS NOT NULL ;

ComparableMPX:

load datecol&[Code Site]&code4 as CompKey ,Comp

;

SQL SELECT DISTINCT [Date Time] as datecol

,[Code Site]

,[Code Rayon] as code4

, Comp

FROM [dbo].[Budget MPX];

Mapcomp:

Mapping Load CompKey ,Comp Resident ComparableMPX ;

Permanent :

LOAD [Code Site]

,[Code Article]

,[PrixPermanentTTC]

,[PrixPermanentHT]

,[Date debut] AS DateDebutPermanent

,[Date fin]AS DateFinPermanent;

SQL SELECT distinct [Code Site]

,[Code Article]

,[PrixVente]as PrixPermanentTTC

,[PrixVenteHT]as PrixPermanentHT

, ([Date debut]) as [Date debut] , ([Date fin ])as [Date fin]

FROM [dbo].[PrixVentePermanent]

;

RemisePromo :

LOAD [Code Article]

,[Valeur]

,[Date debut] AS DateDebutRemisePromo

,[Date fin]AS DateFinRemisePromo;

SQL SELECT distinct [Code Article]

,[Valeur]

, ([Date debut]) as [Date debut] , ([Date fin]) as [Date fin]

FROM [dbo].[RemisePromo]

;

PermanentNationalMPX :

LOAD [Code Article]

,[PrixPermanentNationalMPXTTC]

,PrixPermanentNationalMPXHT

,[Date debut] AS DateDebutPermanentNationalMPX

,[Date fin]AS DateFinPermanentNationalMPX;

SQL SELECT distinct [Code Article]

,[PrixVente] as PrixPermanentNationalMPXTTC

,[PrixVenteHT] as PrixPermanentNationalMPXHT

, ([Date debut]) as [Date debut] , ([Date fin]) as [Date fin]

FROM [dbo].[PrixVentePermanentNationalMPX]

;

RemiseHabituelle :

LOAD [Code Article]

,[PrixRevientRemHabHT]

,PrixRevientRemHabTTC

,[Date debut] AS DateDebutRemiseHabituelle

,[Date fin]AS DateFinRemiseHabituelle;

SQL SELECT distinct [Code Article]

,[PrixRevientHT] as PrixRevientRemHabHT

,[PrixRevientTTC] as PrixRevientRemHabTTC

, ([Date debut]) as [Date debut] , ([Date fin]) as [Date fin]

FROM [dbo].[RemiseHabituelle]

;

RemiseHabituellePromo :

LOAD [Code Article]

,[PrixRevientRemHabPromoHT]

,PrixRevientRemHabPromoTTC

,[Date debut] AS DateDebutRemiseHabituellePromo

,[Date fin]AS DateFinRemiseHabituellePromo;

SQL SELECT distinct [Code Article]

,[PrixRevientHT] as PrixRevientRemHabPromoHT

,[PrixRevientTTC] as PrixRevientRemHabPromoTTC

, ([Date debut]) as [Date debut] , ([Date fin]) as [Date fin]

FROM [dbo].[RemiseHabituellePromo]

;

VenteGeanttmp:

Load

([Prix de vente TTC]/Quantité) as PrixUnitaireArticle,

If(Alimentaire=1 ,([Prix de vente TTC]/Quantité)/(1+TVA/100),([Prix de vente TTC]/Quantité)) as PrixArticleHT,

If(Alimentaire=1,[Prix de Revient]*(1+TVA/100),[Prix de Revient])as PrixRevientTTC,

(If(Alimentaire=1 ,([Prix de vente TTC]/Quantité)/(1+TVA/100),([Prix de vente TTC]/Quantité))) - [Prix de Revient] as MargeHT,

([Prix de vente TTC]/Quantité)- ( If(Alimentaire=1,[Prix de Revient]*(1+TVA/100),[Prix de Revient])) as MargeTTC,

[Prix de Revient]* Quantité as ValeurRevientHT,

(If(Alimentaire=1,[Prix de Revient]*(1+TVA/100),[Prix de Revient])) * Quantité as ValeurRevientTTC,

((If(Alimentaire=1 ,([Prix de vente TTC]/Quantité)/(1+TVA/100),([Prix de vente TTC]/Quantité))) - [Prix de Revient]) * Quantité as ValeurMargeHT,

(([Prix de vente TTC]/Quantité)- ( If(Alimentaire=1,[Prix de Revient]*(1+TVA/100),[Prix de Revient])))* Quantité as ValeurMargeTTC,

(If(Alimentaire=1 ,([Prix de vente TTC]/Quantité)/(1+TVA/100),([Prix de vente TTC]/Quantité)))* Quantité as PrixVenteHT,

ID as IDGeant,

N°tickets,

EAN,

[Code Caisse],

[Code Site],

[Prix de vente TTC],

Quantité,

[Date Time],

DateOfHire as DateOfHireGeant,

Promo,

[Code Article],

[Code Structure],

Alimentaire,

[Prix de Revient],

TVA,

Date([Date Time])&[Code Site] as DivKey,

Date([Date Time]) as Date,

REPLACE([N°tickets]&[Code Caisse]&Year([Date Time])&Month([Date Time])&Day([Date Time]), ' ', '')as nbrClientGeant,

KeyVenteSite,

code4 ,

datecol

;

SQL SELECT V.[N°tickets], V.[Code Caisse], V.[Code Site], V.[Prix de vente TTC] ,

V.Quantité , DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0) as [Date Time], V.ID, V.DateOfHire,

V.EAN ,V.[Code Article], V.TVA , V.Alimentaire , V.[Code Structure], V.[Prix de Revient] ,

Case when V.[Code Article] in (SELECT [Code Article]

FROM [Promotion Geant]

where CONVERT(DATE, V.[Date Time]) between

[Date Debut Promo] and [Date Fin Promo])then 1 else 0 end as Promo,

KeyVenteSite,code4 ,

datecol

FROM dbo.[Vente Geant] V

where year([Date Time])=year(getdate())

;

STORE VenteGeanttmp into 'E:\QVD\QVD\VenteGeantFULL.qvd';

Drop Table VenteGeanttmp;

VenteGeant:

LOAD PrixUnitaireArticle,

PrixArticleHT,

PrixRevientTTC,

MargeHT,

MargeTTC,

ValeurRevientHT,

ValeurRevientTTC,

ValeurMargeHT,

ValeurMargeTTC,

PrixVenteHT,

IDGeant,

N°tickets,

EAN,

[Code Caisse],

[Code Site],

[Prix de vente TTC],

Quantité,

[Date Time],

DateOfHireGeant,

Promo,

[Code Article],

[Code Structure],

Alimentaire,

[Prix de Revient],

TVA,

DivKey,

nbrClientGeant,

KeyVenteSite,

code4 ,

Date(Date#(Date,'YYYY-MM-DD'),'YYYYMMDD') as Date ,

Date#(Date,'YYYY-MM-DD') as DateTest ,

'O' as Comp,

datecol

FROM

E:\QVD\QVD\VenteGeantFULL.qvd

(qvd)

Where (isnull([Code Article]) = 0) and (isnull([Code Structure]) = 0) and (isnull([Prix de Revient])=0);

LEFT JOIN(VenteGeant)

IntervalMatch (datecol)

LOAD DISTINCT [DATE DEBUT]

,[DATE FIN]

resident OCGeant

;

LEFT JOIN (VenteGeant)

LOAD *

RESIDENT OCGeant;

LEFT JOIN(VenteGeant)

IntervalMatch (datecol, [Code Site], [Code Article])

LOAD DISTINCT DateDebutPermanent, DateFinPermanent, [Code Site], [Code Article]

resident Permanent

;

LEFT JOIN (VenteGeant)

LOAD *

RESIDENT Permanent;

LEFT JOIN(VenteGeant)

IntervalMatch (datecol,[Code Article])

LOAD DISTINCT DateDebutRemisePromo,DateFinRemisePromo,[Code Article]

resident RemisePromo ;

LEFT JOIN (VenteGeant)

LOAD *

RESIDENT RemisePromo;

STORE VenteGeant into 'E:\QVD\QVD\VenteGeant.qvd';

Drop Table VenteGeant;

VenteMpxtmp:

Load

([Prix de vente TTC]/Quantité) as PrixUnitaireArticle,

If(Alimentaire=1 ,([Prix de vente TTC]/Quantité)/(1+TVA/100),([Prix de vente TTC]/Quantité)) as PrixArticleHT,

If(Alimentaire=1,[Prix de Revient]*(1+TVA/100),[Prix de Revient])as PrixRevientTTC,

(If(Alimentaire=1 ,([Prix de vente TTC]/Quantité)/(1+TVA/100),([Prix de vente TTC]/Quantité))) - [Prix de Revient] as MargeHT,

([Prix de vente TTC]/Quantité)- ( If(Alimentaire=1,[Prix de Revient]*(1+TVA/100),[Prix de Revient])) as MargeTTC,

[Prix de Revient]* Quantité as ValeurRevientHT,

(If(Alimentaire=1,[Prix de Revient]*(1+TVA/100),[Prix de Revient])) * Quantité as ValeurRevientTTC,

((If(Alimentaire=1 ,([Prix de vente TTC]/Quantité)/(1+TVA/100),([Prix de vente TTC]/Quantité))) - [Prix de Revient]) * Quantité as ValeurMargeHT,

(([Prix de vente TTC]/Quantité)- ( If(Alimentaire=1,[Prix de Revient]*(1+TVA/100),[Prix de Revient])))* Quantité as ValeurMargeTTC,

(If(Alimentaire=1 ,([Prix de vente TTC]/Quantité)/(1+TVA/100),([Prix de vente TTC]/Quantité)))* Quantité as PrixVenteHT,

ID as IDMPX,

N°tickets,

EAN,

[Code Caisse],

[Code Site],

[Prix de vente TTC],

Quantité,

[Date Time],

DateOfHire as DateOfHireMPX,

Promo,

[Code Article],

[Code Structure],

Alimentaire,

[Prix de Revient],

TVA,

Date([Date Time])&[Code Site] as DivKey,

Date([Date Time]) as Date,

//AutoNumber(N°tickets&[Code Site]&[Code Caisse]&Date([Date Time])) as nbrClientMPX,

REPLACE([N°tickets]&[Code Site]&[Code Caisse]&Year([Date Time])&Month([Date Time])&Day([Date Time]), ' ', '')as nbrClientMPX,

KeyVenteSite,

code4 ,

datecol

;

SQL SELECT V.[N°tickets], V.[Code Caisse], V.[Code Site], V.[Prix de vente TTC] ,

V.Quantité , DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0) as [Date Time], V.ID, V.DateOfHire, V.Promo,

V.EAN ,V.[Code Article], V.TVA , V.Alimentaire , V.[Code Structure],V.[Prix de Revient] ,KeyVenteSite

,

code4 ,

datecol

FROM dbo.[Vente MPX] V

where year([Date Time])=year(getdate())

;

STORE VenteMpxtmp into 'E:\QVD\QVD\VenteMpxFull.qvd';

Drop Table VenteMpxtmp;

VenteMpx:

LOAD PrixUnitaireArticle,

PrixArticleHT,

PrixRevientTTC,

MargeHT,

MargeTTC,

ValeurRevientHT,

ValeurRevientTTC,

ValeurMargeHT,

ValeurMargeTTC,

PrixVenteHT,

N°tickets,

EAN,

[Code Caisse],

[Code Site],

[Prix de vente TTC],

Quantité,

[Date Time],

Promo,

[Code Article],

[Code Structure],

Alimentaire,

[Prix de Revient],

TVA,

DivKey,

IDMPX,

DateOfHireMPX,

nbrClientMPX,

KeyVenteSite,

code4 ,

datecol,

Date(Date#(Date,'YYYY-MM-DD'),'YYYYMMDD') as Date ,

Date#(Date,'YYYY-MM-DD') as DateTest ,

ApplyMap('MapComp', datecol&[Code Site]&code4 , 'O') as Comp

FROM

E:\QVD\QVD\VenteMpxFull.qvd

(qvd)

Where (isnull([Code Article]) = 0) and (isnull([Code Structure]) = 0) and (isnull([Prix de Revient])=0);

LEFT JOIN(VenteMpx)

IntervalMatch (datecol)

LOAD DISTINCT DEBUT

,FIN

resident OCMPX

;

LEFT JOIN (VenteMpx)

LOAD *

RESIDENT OCMPX;

LEFT JOIN(VenteMpx)

IntervalMatch (datecol, [Code Site], [Code Article])

LOAD DISTINCT DateDebutPermanent, DateFinPermanent, [Code Site], [Code Article]

resident Permanent

;

LEFT JOIN (VenteMpx)

LOAD *

RESIDENT Permanent;

LEFT JOIN(VenteMpx)

IntervalMatch (datecol,[Code Article])

LOAD DISTINCT DateDebutRemisePromo,DateFinRemisePromo,[Code Article]

resident RemisePromo

;

LEFT JOIN (VenteMpx)

LOAD *

RESIDENT RemisePromo;

LEFT JOIN(VenteMpx)

IntervalMatch (datecol,[Code Article])

LOAD DISTINCT DateDebutPermanentNationalMPX, DateFinPermanentNationalMPX,[Code Article]

resident PermanentNationalMPX

;

LEFT JOIN (VenteMpx)

LOAD *

RESIDENT PermanentNationalMPX;

LEFT JOIN(VenteMpx)

IntervalMatch (datecol,[Code Article])

LOAD DISTINCT DateDebutRemiseHabituelle,DateFinRemiseHabituelle,[Code Article]

resident RemiseHabituelle

;

LEFT JOIN (VenteMpx)

LOAD *

RESIDENT RemiseHabituelle;

LEFT JOIN(VenteMpx)

IntervalMatch (datecol,[Code Article])

LOAD DISTINCT DateDebutRemiseHabituellePromo, DateFinRemiseHabituellePromo,[Code Article]

resident RemiseHabituellePromo

;

LEFT JOIN (VenteMpx)

LOAD *

RESIDENT RemiseHabituellePromo;

STORE VenteMpx into 'E:\QVD\QVD\VenteMpx.qvd';

Drop Table VenteMpx;

BudgetMpxtmp:

Load

[idObjCA]

,[CA TTC]

,[VAL MRG TTC]

,[CA HT]

,[VAL MRG HT]

,[Rayon]

,[Date Time]

,[Code Site]

,[Code Rayon]

,[Code Structure]

,Comp

;

SQL SELECT [idObjCA]

,[CA TTC]

,[VAL MRG TTC]

,[CA HT]

,[VAL MRG HT]

,[Rayon]

,DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0) as [Date Time]

,[Code Site]

,[Code Rayon]

,[Code Structure]

,Comp

FROM [QlikDataWarehouse].[dbo].[Budget MPX];

STORE BudgetMpxtmp into 'E:\QVD\QVD\BudgetMpx.qvd';

Drop Table BudgetMpxtmp;

BudgetMpx:

LOAD [idObjCA]

,[CA TTC]

,[VAL MRG TTC]

,[CA HT]

,[VAL MRG HT]

,[Rayon]

,[Date Time]

,[Code Site]

,[Code Rayon]

,[Code Structure]

,Comp

FROM

'E:\QVD\BudgetMpx.qvd'

(qvd)

;

STORE BudgetMpx into 'E:\QVD\QVD\BudgetMpx.qvd';

Drop Table BudgetMpx;

BudgetGeanttmp:

Load

[idObjCA]

,[CA TTC]

,[VAL MRG TTC]

,[CA HT]

,[VAL MRG HT]

,[Rayon]

,[Date Time]

,[Code Site]

,[Code Rayon]

,[Code Structure]

,Comp

;

SQL SELECT [idObjCA]

,[CA TTC]

,[VAL MRG TTC]

,[CA HT]

,[VAL MRG HT]

,[Rayon]

,DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0) as [Date Time]

,[Code Site]

,[Code Rayon]

,[Code Structure]

,Comp

FROM [QlikDataWarehouse].[dbo].[Budget Geant]

;

STORE BudgetGeanttmp into 'E:\QVD\QVD\BudgetGeant.qvd';

Drop Table BudgetGeanttmp;

BudgetGeant:

LOAD [idObjCA]

,[CA TTC]

,[VAL MRG TTC]

,[CA HT]

,[VAL MRG HT]

,[Rayon]

,[Date Time]

,[Code Site]

,[Code Rayon]

,[Code Structure]

,Comp

FROM

'E:\QVD\QVD\BudgetGeant.qvd'

(qvd)

;

STORE BudgetGeant into 'E:\QVD\QVD\BudgetGeant.qvd';

Drop Table BudgetGeant;

drop table OCMPX;

drop table ComparableMPX;

drop table RemiseHabituelle;

drop table RemiseHabituellePromo;

drop table PermanentNationalMPX;

drop table RemisePromo;

drop table Permanent;

drop table OCGeant;

Casse:

load [Code Site],[Code Article],[Date] as [Date Time]

,casse

,QuantiteCasse

,[Code Structure]

;

SQL SELECT CASE WHEN [Code Site]IN( 9001 ,9002) then 90012 else [Code Site] end as [Code Site]

,[Code Article]

,[Quantite]*PrixRevientTTC as casse

,Quantite as QuantiteCasse

,[Date]

,[Code Structure]

FROM [dbo].[Casse];

STORE Casse into 'E:\QVD\QVD\Casse.qvd';

Drop Table Casse;

Ajust :

load [Code Site],[Code Article],[Date] as [Date Time]

,ajust

,QuantiteAjust

,[Code Structure]

;

SQL SELECT CASE WHEN [Code Site]IN( 9001 ,9002) then 90012 else [Code Site] end as [Code Site]

,[Code Article]

,[Quantite]*PrixRevientTTC as ajust

,Quantite as QuantiteAjust

,[Date]

,[Code Structure]

FROM [dbo].[ajust];

STORE Ajust into 'E:\QVD\QVD\Ajust.qvd';

Drop Table Ajust;

Anonymous · ‎2016-10-26

hi gysbert,

would you please take a look and help me

ty

Anil_Babu_Samineni · ‎2016-10-26

Can you share Log file, please? It might cause time spirit using Left Join. Try to share Log file with us. Thanks

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Peter_Cammaert · ‎2016-10-27

Leave the script as-is (problematic JOIN at the end) but run it in the debugger and select a limited load on the left (for example 1000 rows). The theoretical maximum of records to be produced by the last JOIN is now 1 million. Create table boxes to check whether the JOIN has peen correctly programmed. You're probably lacking a key field and thereby creating some sort of cartesian product.

Peter