Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
souadouert
Specialist
Specialist

concatination two table

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;


21 Replies
souadouert
Specialist
Specialist
Author