Usually, you should use alias (as) sentence to rename field and avoid the use qualify or unqualify, using it only if there is really neccesary, so for each field you create a unique name, ie:
LOAD "DOC_PIECE" as [Document Piece]
...
For those fields you want to use to join tables you set the same name in the tables you want to join, ie:
LOAD DEV_CODE as IdDEV
And for tables with more than one key you can create a concatenated key:
LOAD DOC_NUMERO & '#' & DEV_CODE as IdDoc
There are more tricks to keep a good data model, but all this comes with experience.
Anyway, I make a little changes in your script keeping the qualify/unqualify approach, i can't test it so maybe there is an error.
Hope this helps!
SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='# ##0,00 €;-# ##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';
SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';
/* *************** Activation des options globales au document *************** */
/* Connexion OLEDB SQL Server 2014 */
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ICP;Data Source=***************;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=********************;Use Encryption for Data=False;Tag with column collation when possible=False];
/*******Variables ******/
set vSaveQVD = ;
DOCUMENTS:
LOAD "DOC_DATE",
"DEV_CODE",
"DIV_CODE",
"DOC_DT_PRV",
"DOC_EN_TTC",
"DOC_ETAT",
"DOC_NUMERO",
"DOC_PIECE",
"DOC_STYPE",
"DOC_TX_DEV",
"DOC_TXRFAC",
"DOC_TYPE",
"NAT_CODE",
"PAY_CODE",
"PCF_CODE",
"PCF_PAYEUR",
"PCF_REMMIN",
"PCF_REMVAL",
"PRJ_CODE",
"REG_CODE",
"REP_CODE",
"SAL_CODE",
"SRV_CODE",
"TAR_CODE";
SQL SELECT DOC_DATE,
DEV_CODE,
DIV_CODE,
DOC_DT_PRV,
DOC_EN_TTC,
DOC_ETAT,
DOC_NUMERO,
DOC_PIECE,
DOC_STYPE,
DOC_TX_DEV,
DOC_TXRFAC,
DOC_TYPE,
NAT_CODE,
PAY_CODE,
PCF_CODE,
PCF_PAYEUR,
PCF_REMMIN,
PCF_REMVAL,
PRJ_CODE,
REG_CODE,
REP_CODE,
SAL_CODE,
SRV_CODE,
TAR_CODE
FROM ICP.dbo.DOCUMENTS;
STORE DOCUMENTS into $(vSaveQVD) DOCUMENTS.QVD(qvd);
DROP TABLE DOCUMENTS;
QUALIFY *;
UNQUALIFY "DOC_NUMERO",
"DEV_CODE",
"DIV_CODE",
"PAY_CODE",
"PCF_CODE",
"REG_CODE",
"REP_CODE",
"SAL_CODE",
"SRV_CODE",
"TAR_CODE";
DOCUMENTS:
LOAD "DOC_DATE",
Month(DOC_DATE) as [Mois],
Year("DOC_DATE") as [Année],
Date("DOC_DATE") as [Date],
If(DOC_TYPE = 'A', 'Achat', if(DOC_TYPE = 'V', 'Ventes')) as [Type de doc],
If(DOC_TYPE = 'V',
if(DOC_STYPE = 'P', 'Pro-forma',
if(DOC_STYPE = 'D', 'Devis',
if(DOC_STYPE = 'C', 'Commandes',
if(DOC_STYPE = 'B', 'Bons de Livraison',
if(DOC_STYPE = 'R', 'Bons de Retour',
if(DOC_STYPE = 'F', 'Factures',
if(DOC_STYPE = '1', 'Factures Financières',
if(DOC_STYPE = 'A', 'Avoirs',
if(DOC_STYPE = '0', 'Avoirs Financiers',
))))))))),
if(DOC_TYPE = 'A',
if(DOC_STYPE = 'D', 'Demandes de Prix',
if(DOC_STYPE = 'C', 'Commandes',
if(DOC_STYPE = 'B', 'Bons de Réceptions',
if(DOC_STYPE = 'R', 'Bons de Retour',
if(DOC_STYPE = 'F', 'Factures',
if(DOC_STYPE = '1', 'Factures Financières',
if(DOC_STYPE = 'A', 'Avoirs',
if(DOC_STYPE = '0', 'Avoirs Financiers')
))))))))
) as [Sous_Type_de_doc],
"DEV_CODE",
"DIV_CODE",
"DOC_DT_PRV",
"DOC_EN_TTC",
"DOC_ETAT",
"DOC_NUMERO",
"DOC_PIECE",
"DOC_STYPE",
"DOC_TX_DEV",
"DOC_TXRFAC",
"DOC_TYPE",
"NAT_CODE",
"PAY_CODE",
"PCF_CODE",
"PCF_PAYEUR",
"PCF_REMMIN",
"PCF_REMVAL",
"PRJ_CODE",
"REG_CODE",
"REP_CODE",
"SAL_CODE",
"SRV_CODE",
"TAR_CODE"
FROM $(vSaveQVD) DOCUMENTS.QVD(qvd);
UNQUALIFY *;
DEVISES:
LOAD "DEV_CODE",
"DEV_LIB",
"DEV_MONEY",
"DEV_MONEYS",
"DEV_FORMAT",
"DEV_NB_DEC",
"DEV_SYMBOL",
"DEV_DT_ACT",
"DEV_INCERT",
"DEV_COURS",
"DEV_DTEURO",
"DEV_EURO",
"DEV_DORT",
"DEV_DTMAJ",
"DEV_USRMAJ",
"DEV_NUMMAJ";
SQL SELECT DEV_CODE,
DEV_LIB,
DEV_MONEY,
DEV_MONEYS,
DEV_FORMAT,
DEV_NB_DEC,
DEV_SYMBOL,
DEV_DT_ACT,
DEV_INCERT,
DEV_COURS,
DEV_DTEURO,
DEV_EURO,
DEV_DORT,
DEV_DTMAJ,
DEV_USRMAJ,
DEV_NUMMAJ
FROM ICP.dbo.DEVISES;
STORE DEVISES into $(vSaveQVD) DEVISES.QVD(qvd);
DROP TABLE DEVISES;
QUALIFY *;
UNQUALIFY "DEV_CODE";
DEVISES:
LOAD DEV_CODE,
DEV_LIB,
DEV_MONEY,
DEV_MONEYS,
DEV_FORMAT,
DEV_NB_DEC,
DEV_SYMBOL,
DEV_DT_ACT,
DEV_INCERT,
DEV_COURS,
DEV_DTEURO,
DEV_EURO,
DEV_DORT,
DEV_DTMAJ,
DEV_USRMAJ,
DEV_NUMMAJ
FROM $(vSaveQVD) DEVISES.QVD(qvd);
LIGNES:
LOAD "DOC_NUMERO",
"LIG_NUMERO",
"LIG_SUBNUM",
"LIG_QTE",
"REP_CODE",
"ART_CODE",
"ART_NCOLIS",
"ART_TGAMME",
"DEP_CODE",
"PRJ_CODE",
"SAL_CODE",
"TAR_CODE",
"LIG_P_BRUT",
"LIG_TOTAL",
"NAT_TVATX",
"LIG_COUT";
SQL SELECT "DOC_NUMERO",
"LIG_NUMERO",
"LIG_SUBNUM",
"LIG_QTE",
"REP_CODE",
"ART_CODE",
"ART_NCOLIS",
"ART_TGAMME",
"DEP_CODE",
"PRJ_CODE",
"SAL_CODE",
"TAR_CODE",
"LIG_P_BRUT",
"LIG_TOTAL",
"NAT_TVATX",
"LIG_COUT"
FROM ICP.dbo.LIGNES;
STORE LIGNES into $(vSaveQVD) LIGNES.QVD(qvd);
DROP TABLE LIGNES;
QUALIFY *;
UNQUALIFY "DOC_NUMERO",
"LIG_NUMERO",
"LIG_SUBNUM",
"DEP_CODE",
"PRJ_CODE",
"SAL_CODE",
"TAR_CODE",
"CODEL";
LIGNES:
LOAD AUTONUMBERHASH128(DOC_NUMERO& '|' &LIG_NUMERO& '|' &LIG_SUBNUM) AS ID_LIGNES,
"DOC_NUMERO",
"LIG_NUMERO",
"LIG_SUBNUM",
"LIG_QTE",
"REP_CODE",
"ART_CODE",
"ART_NCOLIS",
"ART_TGAMME",
"DEP_CODE",
"PRJ_CODE",
"SAL_CODE",
"TAR_CODE",
"LIG_P_BRUT",
"LIG_TOTAL",
"NAT_TVATX",
"LIG_COUT",
"LIG_UB" as CODEL
from $(vSaveQVD) LIGNES.QVD(qvd) WHERE LIG_TYPE NOT IN ('x', 'X', 'T');
