Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

souadouert
Contributor III

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

Re: concatination two table

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 (qvd)

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 (qvd);

WHERE not Exists (COMPTE_ID);

Re: concatination two table

resident  ACCOUNT1

//WHERE not Exists (COMPTE_ID)

;

drop table ACCOUNT1;


Is this making any wrong? looks like commenting. Can you disable comment and reload()

souadouert
Contributor III

Re: concatination two table

i tried this option sunny

Re: concatination two table

Is COMPTE_ID a unique identifier from both the tables? What is the issue you are having?

souadouert
Contributor III

Re: concatination two table

YYES sunny

Re: concatination two table

okay, and what about my second question? What is the issue you are having?

souadouert
Contributor III

Re: concatination two table

because load only table A lignes

Re: concatination two table

souad ouertani wrote:

because treload only table A lignes

What?

souadouert
Contributor III

Re: concatination two table

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

Community Browser