I used this script , and i have invalid exression error
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.';
ACCOUNT1:
LOAD
%ID_COMPTE AS COMPTE_ID,
%ID_CLIENT AS CLIENT_ID,
%ID_CLIENT AS COMPTE_CLIENT,
'N_D' AS COMPTE_CATEGORIE,
'N_D' AS COMPTE_INTITULE,
%ID_DEVISE_COMPTE AS COMPTE_DEVISE,
'N_D' AS COMPTE_CODE_LIMITE,
%ID_AGENCE_T24_COMPTE AS COMPTE_AGENCE_T24,
0 AS COMPTE_SOLDE_REEL,
'N_D' AS COMPTE_RECORD_STATUS,
'N_D' AS COMPTE_AUTHORISER,
'N_D' AS COMPTE_AGENCE_COMPTABLE,
'N_D' AS COMPTE_INTERET,
'N_D' AS COMPTE_FUSION,
'N_D' AS COMPTE_GROUPE_CONDITION,
'N_D' AS COMPTE_INACTIV,
0 AS COMPTE_SOLDE_COMPTABLE,
0 AS COMPTE_SOLDE_DISPONIBLE,
'N_D' AS COMPTE_COMMISSION,
DATE_CLOTURE AS COMPTE_DATE_CLOTURE,
OPENING.DATE AS COMPTE_DATE_OUVERTURE,
NATURE_COMPTE AS COMPTE_NATURE,
'N_D' AS COMPTE_PRIME_FIDELITE,
'N_D' AS COMPTE_PORTEFEUILLE,
'N_D' AS COMPTE_DATE_TIME,
ALT.ACCT.ID AS COMPTE_NUM_CTOS,
'N_D' AS COMPTE_RIB,
'N_D' AS COMPTE_NUM_TRANSFER,
0 AS COMPTE_MONTANT_BLOQUE,
'N_D' AS COMPTE_CASH_POOL,
'N_D' AS COMPTE_CATEGORIE_DESCRIPTION,
'N_D' AS COMPTE_LIMITE_DESCRIPTION,
'N_D' AS COMPTE_RESTRICTION,
OPTION.COMPTE AS COMPTE_OPTION,
/*ApplyMap('TYPE_PACK',OPTION.COMPTE,'N_D') AS COMPTE_OPTION_DESCRIPTION,
ApplyMap('NATURE_COMPTE_1',NATURE_COMPTE,'N_D') AS COMPTE_TYPE_DEPOT,
ApplyMap('NATURE_COMPTE_2',NATURE_COMPTE,'N_D') AS COMPTE_SOUS_TYPE_DEPOT,
ApplyMap('NATURE_COMPTE_3',NATURE_COMPTE,'N_D') AS COMPTE_NATURE_TYPE,*/
0 AS COMPTE_SOLDE_COMPTABLE_TND,
0 as TEST
FROM
(qvd);
//WHERE not Exists (COMPTE_ID)
;
ACCOUNT:
LOAD ID AS COMPTE_ID,
CUSTOMER AS CLIENT_ID,
CUSTOMER AS COMPTE_CLIENT,
CATEGORY AS COMPTE_CATEGORIE,
ACCOUNT_TITLE_1 AS COMPTE_INTITULE,
CURRENCY AS COMPTE_DEVISE,
LIMIT_REF AS COMPTE_CODE_LIMITE,
ACCOUNT_OFFICER AS COMPTE_AGENCE_T24,
if(len(trim(ONLINE_ACTUAL_BAL))=0,0,ONLINE_ACTUAL_BAL) AS COMPTE_SOLDE_REEL,
RECORD_STATUS AS COMPTE_RECORD_STATUS,
AUTHORISER AS COMPTE_AUTHORISER,
CO_CODE AS COMPTE_AGENCE_COMPTABLE,
//POSTING_RESTRICT AS COMPTE_RESTRICTION,
INTEREST_LIQU_ACCT AS COMPTE_INTERET,
if(len(trim(INTEREST_COMP_ACCT))=0,'N_D',INTEREST_COMP_ACCT) AS COMPTE_FUSION,
CONDITION_GROUP AS COMPTE_GROUPE_CONDITION,
INACTIV_MARKER AS COMPTE_INACTIV,
if(len(trim(OPEN_ACTUAL_BAL))=0,0,OPEN_ACTUAL_BAL) AS COMPTE_SOLDE_COMPTABLE,
if(len(trim(WORKING_BALANCE))=0,0,WORKING_BALANCE) AS COMPTE_SOLDE_DISPONIBLE,
CHARGE_ACCOUNT AS COMPTE_COMMISSION,
CLOSURE_DATE AS COMPTE_DATE_CLOTURE,
OPENING_DATE AS COMPTE_DATE_OUVERTURE,
OPTION_COMPTE AS COMPTE_OPTION,
NATURE_COMPTE AS COMPTE_NATURE,
PREMIUM_TYPE AS COMPTE_PRIME_FIDELITE,
OTHER_OFFICER AS COMPTE_PORTEFEUILLE,
DATE_TIME AS COMPTE_DATE_TIME,
NUM_CTOS AS COMPTE_NUM_CTOS,
RIB AS COMPTE_RIB,
NUM_TRANSFER AS COMPTE_NUM_TRANSFER,
if(len(trim(LOCKED_AMOUNT))=0,0,LOCKED_AMOUNT) AS COMPTE_MONTANT_BLOQUE,
CASH_POOL_GROUP AS COMPTE_CASH_POOL,
/*ApplyMap('CATEGORIE_PRODUIT',CATEGORY,'N_D') AS COMPTE_CATEGORIE_DESCRIPTION,
ApplyMap('LIMIT_REFERENCE',LIMIT_REF,'N_D') AS COMPTE_LIMITE_DESCRIPTION,
ApplyMap('POSTING_RESTRICT',POSTING_RESTRICT,'N_D') AS COMPTE_RESTRICTION,
ApplyMap('TYPE_PACK',OPTION_COMPTE,'N_D') AS COMPTE_OPTION_DESCRIPTION,
ApplyMap('NATURE_COMPTE_1',NATURE_COMPTE,'N_D') AS COMPTE_TYPE_DEPOT,
ApplyMap('NATURE_COMPTE_2',NATURE_COMPTE,'N_D') AS COMPTE_SOUS_TYPE_DEPOT,
ApplyMap('NATURE_COMPTE_3',NATURE_COMPTE,'N_D') AS COMPTE_NATURE_TYPE,
OPEN_ACTUAL_BAL * ApplyMap('CURRENCY',CURRENCY,0) /ApplyMap('UNITE_DEVISE',CURRENCY,1) AS COMPTE_SOLDE_COMPTABLE_TND,*/
1 AS TEST
FROM
(qvd)
WHERE not (CATEGORY like '5*');
Concatenate
LOAD
distinct COMPTE_ID ,
CLIENT_ID,
COMPTE_CLIENT,
COMPTE_CATEGORIE,
COMPTE_INTITULE,
COMPTE_DEVISE,
COMPTE_CODE_LIMITE,
COMPTE_AGENCE_T24,
COMPTE_SOLDE_REEL,
COMPTE_RECORD_STATUS,
COMPTE_AUTHORISER,
COMPTE_AGENCE_COMPTABLE,
COMPTE_INTERET,
COMPTE_FUSION,
COMPTE_GROUPE_CONDITION,
COMPTE_INACTIV,
COMPTE_SOLDE_COMPTABLE,
COMPTE_SOLDE_DISPONIBLE,
COMPTE_COMMISSION,
COMPTE_DATE_CLOTURE,
COMPTE_DATE_OUVERTURE,
COMPTE_NATURE,
COMPTE_PRIME_FIDELITE,
COMPTE_PORTEFEUILLE,
COMPTE_DATE_TIME,
COMPTE_NUM_CTOS,
COMPTE_RIB,
COMPTE_NUM_TRANSFER,
COMPTE_MONTANT_BLOQUE,
COMPTE_CASH_POOL,
COMPTE_CATEGORIE_DESCRIPTION,
COMPTE_LIMITE_DESCRIPTION,
COMPTE_RESTRICTION,
COMPTE_OPTION,
/*ApplyMap('TYPE_PACK',OPTION.COMPTE,'N_D') AS COMPTE_OPTION_DESCRIPTION,
ApplyMap('NATURE_COMPTE_1',NATURE_COMPTE,'N_D') AS COMPTE_TYPE_DEPOT,
ApplyMap('NATURE_COMPTE_2',NATURE_COMPTE,'N_D') AS COMPTE_SOUS_TYPE_DEPOT,
ApplyMap('NATURE_COMPTE_3',NATURE_COMPTE,'N_D') AS COMPTE_NATURE_TYPE,*/
COMPTE_SOLDE_COMPTABLE_TND,
TEST
resident ACCOUNT1
WHERE not Exists (COMPTE_ID)
;
drop table ACCOUNT1;
accountfinal:
LOAD
COMPTE_ID ,
CLIENT_ID,
COMPTE_CLIENT,
COMPTE_CATEGORIE,
COMPTE_INTITULE,
COMPTE_DEVISE,
COMPTE_CODE_LIMITE,
COMPTE_AGENCE_T24,
COMPTE_SOLDE_REEL,
COMPTE_RECORD_STATUS,
COMPTE_AUTHORISER,
COMPTE_AGENCE_COMPTABLE,
COMPTE_INTERET,
COMPTE_FUSION,
COMPTE_GROUPE_CONDITION,
COMPTE_INACTIV,
COMPTE_SOLDE_COMPTABLE,
COMPTE_SOLDE_DISPONIBLE,
COMPTE_COMMISSION,
COMPTE_DATE_CLOTURE,
COMPTE_DATE_OUVERTURE,
COMPTE_NATURE,
COMPTE_PRIME_FIDELITE,
COMPTE_PORTEFEUILLE,
COMPTE_DATE_TIME,
COMPTE_NUM_CTOS,
COMPTE_RIB,
COMPTE_NUM_TRANSFER,
COMPTE_MONTANT_BLOQUE,
COMPTE_CASH_POOL,
COMPTE_CATEGORIE_DESCRIPTION,
COMPTE_LIMITE_DESCRIPTION,
COMPTE_RESTRICTION,
COMPTE_OPTION,
/*ApplyMap('TYPE_PACK',OPTION.COMPTE,'N_D') AS COMPTE_OPTION_DESCRIPTION,
ApplyMap('NATURE_COMPTE_1',NATURE_COMPTE,'N_D') AS COMPTE_TYPE_DEPOT,
ApplyMap('NATURE_COMPTE_2',NATURE_COMPTE,'N_D') AS COMPTE_SOUS_TYPE_DEPOT,
ApplyMap('NATURE_COMPTE_3',NATURE_COMPTE,'N_D') AS COMPTE_NATURE_TYPE,*/
COMPTE_SOLDE_COMPTABLE_TND,
max(TEST) as test
resident ACCOUNT
group by COMPTE_ID;
store * from ACCOUNT into account_final.qvd (qvd)
