Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two fact tables budget and vente , I am looking to integrate data in Qlikview . I used a link table . Yje following script returnrs wrong data , How to correct it ?
Temp_Vente:
LOAD PrixUnitaireArticle,
PrixArticleHT,
PrixRevientTTC,
MargeHT,
MargeTTC,
ValeurRevientHT,
ValeurRevientTTC,
ValeurMargeHT,
ValeurMargeTTC,
PrixVenteHT,
ID,
N°tickets,
EAN,
[Code Caisse],
[Code Site],
[Prix de vente TTC],
Quantité,
[Date Time],
DateOfHire,
Promo,
[Code Article],
[Code Structure],
Alimentaire,
[Prix de Revient],
TVA,
DivKey,
Date,
nbrClient,
KeyVenteSite
FROM
E:\QVD TEST\VenteFULL.qvd
(qvd);
Concatenate
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,
Date,
IDMPX,
DateOfHireMPX,
nbrClientMPX,
KeyVenteSite
FROM
E:\QVD TEST\VenteMpxFull.qvd
(qvd);
[Vente]:
LOAD [Code Caisse] & '_' & [Code Site] & '_' &[Date Time] & '_' &[Code Article] & '_' & Date & '_' &[Code Structure]& '_' &IDMPX & '_' &DateOfHireMPX & '_' &KeyVenteSite& '_' & DivKey as test_key1,
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,
// Date,
// IDMPX,
// DateOfHireMPX,
nbrClientMPX
// KeyVenteSite
RESIDENT [Temp_Vente];
Temp_Budget :
LOAD
[idObjCA]
,[CA TTC]
,[VAL MRG TTC]
,[CA HT]
,[VAL MRG HT]
,[Rayon]
,[Date Time]
,[Code Site]
,[Code Rayon]
FROM
E:\QVD TEST\BudgetMpx.qvd
(qvd);
[Budget]:
LOAD Rayon & '_' & [Date Time] & '_' &[Code Site] & '_' &[Code Rayon]& '_' & idObjCA as test_key,
[CA TTC]
,[VAL MRG TTC]
,[CA HT]
,[VAL MRG HT]
RESIDENT [Temp_Budget];
[temp_link_table]:
LOAD DISTINCT
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,
Date,
IDMPX,
DateOfHireMPX,
nbrClientMPX,
KeyVenteSite
RESIDENT [Temp_Vente];
CONCATENATE ([temp_link_table])
LOAD DISTINCT
[idObjCA]
,[CA TTC]
,[VAL MRG TTC]
,[CA HT]
,[VAL MRG HT]
,[Rayon]
, [Date Time]
,[Code Site]
,[Code Rayon]
RESIDENT [Temp_Budget];
/**
* The final Link Table will contain all of the individual keys one time as well as your concatenated keys
*/
[link_table]:
LOAD DISTINCT
Rayon ,[Code Rayon],idObjCA,DivKey,
[Code Caisse], [Code Site] ,[Date Time] ,[Code Article], Date,[Code Structure],IDMPX ,DateOfHireMPX ,KeyVenteSite,
Rayon & '_' & [Date Time] & '_' &[Code Site] & '_' &[Code Rayon]& '_' & idObjCA as test_key,
[Code Caisse] & '_' & [Code Site] & '_' &[Date Time] & '_' &[Code Article] & '_' & Date & '_' &[Code Structure]& '_' &IDMPX & '_' &DateOfHireMPX & '_' &KeyVenteSite& '_' & DivKey as test_key1
RESIDENT [temp_link_table];
DROP TABLE [Temp_Vente];
DROP TABLE [Temp_Budget];
DROP TABLE [temp_link_table];