topic Re: concatination two table in QlikView

concatination two table

souadouert — Mon, 08 Jan 2018 13:46:29 GMT

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;

Re: concatination two table

sunny_talwar — Mon, 08 Jan 2018 13:51:10 GMT

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

Anil_Babu_Samineni — Mon, 08 Jan 2018 13:52:30 GMT

resident ACCOUNT1

//WHERE not Exists (COMPTE_ID)

;

drop table ACCOUNT1;

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

Re: concatination two table

souadouert — Mon, 08 Jan 2018 14:00:49 GMT

i tried this option sunny

Re: concatination two table

sunny_talwar — Mon, 08 Jan 2018 14:03:28 GMT

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

Re: concatination two table

souadouert — Mon, 08 Jan 2018 14:23:24 GMT

YYES sunny

Re: concatination two table

sunny_talwar — Mon, 08 Jan 2018 14:30:41 GMT

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

Re: concatination two table

souadouert — Mon, 08 Jan 2018 14:33:22 GMT

because load only table A lignes

Re: concatination two table

sunny_talwar — Mon, 08 Jan 2018 14:35:47 GMT

souad ouertani wrote:

because treload only table A lignes

What?

Re: concatination two table

souadouert — Mon, 08 Jan 2018 14:35:57 GMT

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

Re: concatination two table

sunny_talwar — Mon, 08 Jan 2018 14:39:29 GMT

I believe that this is what it should be doing as long as COMPTE_ID determines the intersection and is unique between the two sources

Re: concatination two table

souadouert — Mon, 08 Jan 2018 14:41:36 GMT

No sunny i need also load the rest of B lignes

Re: concatination two table

sunny_talwar — Mon, 08 Jan 2018 14:45:28 GMT

Hahahaha that is what it should be doing souadouert

LOAD Table A (1248700 Lines)

Concatenate where not exists COMPTE_ID

LOAD Table B (283152 Lines)

Re: concatination two table

souadouert — Mon, 08 Jan 2018 14:47:47 GMT

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)