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

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
souadouert
Specialist
Specialist
‎2018-01-08 08:46 AM

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;


1,957 Views
0 Likes
21 Replies
sunny_talwar
MVP
MVP
‎2018-01-08 09:39 AM

In response to souadouert

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

680 Views
0 Likes
souadouert
Specialist
Specialist
‎2018-01-08 09:41 AM
Author

In response to sunny_talwar

No sunny i need also load the rest of  B lignes

680 Views
0 Likes
sunny_talwar
MVP
MVP
‎2018-01-08 09:45 AM

In response to souadouert

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)

680 Views
0 Likes
souadouert
Specialist
Specialist
‎2018-01-08 09:47 AM
Author

In response to sunny_talwar

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)

680 Views
0 Likes
souadouert
Specialist
Specialist
‎2018-01-08 09:50 AM
Author

In response to sunny_talwar

the intersection between A and B  is 342 Lignes

So i have to load  283 494 - 342 from table B

680 Views
0 Likes
sunny_talwar
MVP
MVP
‎2018-01-08 09:58 AM

In response to souadouert

But this is not the script that I proposed to you... did you try what I proposed?

680 Views
0 Likes
souadouert
Specialist
Specialist
‎2018-01-08 10:00 AM
Author

In response to sunny_talwar

yes i tried

680 Views
0 Likes
sunny_talwar
MVP
MVP
‎2018-01-08 10:01 AM

In response to souadouert

Can you share the logfile for when you tried my script?

680 Views
0 Likes
souadouert
Specialist
Specialist
‎2018-01-08 10:04 AM
Author

In response to sunny_talwar

table A  1 as test table 2 0 as test  ,
the result
Capture.PNG

680 Views
0 Likes
souadouert
Specialist
Specialist
‎2018-01-08 10:05 AM
Author

In response to sunny_talwar

I dont know when i found  logfile

680 Views
0 Likes