Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two tables A and B with the same structure, so I need to concatinate ATB without redundancy, ie if we have an intersection, I keep the line of table A
I used this script , but i see that not true result
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 COMPTE_STATUT
FROM
(qvd);
;
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 COMPTE_STATUT
//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,
COMPTE_STATUT
resident ACCOUNT1
;
drop table ACCOUNT1;
May be this
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 COMPTE_STATUT
//1 AS TEST
FROM
WHERE not (CATEGORY like '5*');
Concatenate
LOAD distinct
%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 COMPTE_STATUT
FROM
WHERE not Exists (COMPTE_ID);
resident ACCOUNT1
//WHERE not Exists (COMPTE_ID)
;
drop table ACCOUNT1;
Is this making any wrong? looks like commenting. Can you disable comment and reload()
i tried this option sunny
Is COMPTE_ID a unique identifier from both the tables? What is the issue you are having?
YYES sunny
okay, and what about my second question? What is the issue you are having?
because load only table A lignes
souad ouertani wrote:
because treload only table A lignes
What?
TABLE A 1248700 LIGNES
TABLE B 283 494
the intersection bextwenn this table 342 ligne
So the final table has to reload 153 18 52
but in myy case final table 1248700 LIGNES