Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i m trying import the excels of 3 4 tables and i m getting $syn fields and tables created, i have tried concatenate but becuase of concatenate is there any diffrence in the result of join with syn table and with concatenate command?
Please can any one tell me that?
Hi,
Yes sometimes it will affect the data which leads to wrong output.Its better to avoid situations of sync table and loosely coupled tables.
Celambarasan
Ok and you have created the keys based on the new combination of fields coming right?
And now all the joins are on keys right?
Hi,
Yes absolutely.Keys are created to join the tables based on the combination of common fields you have in multiple tables.
Celambarasan
hi neha
instead of using qualify and unqualify keyword you should go for making the same columns one key or you should use as keyword.
dont get confused with as and alias keyword.
alias will rename the column in the whole script while as will do it only in 1 column.
hello,
If anyone can help me please !!
I have a problem loading my tables on Qlikview.
I found a mistake Champ - <DEV_LIB>. Yet the field is in my and my select LOAD. I can not find the error. Could you tell me what should I do?
I could not make the joins between tables
Here is my code:
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.';
/* *************** Activation des options globales au document *************** */
/* Connexion OLEDB SQL Server 2014 */
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ICP;Data Source=***************;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=********************;Use Encryption for Data=False;Tag with column collation when possible=False];
/*******Variables ******/
set vSaveQVD =
DOCUMENTS:
LOAD "DOC_DATE",
"DEV_CODE",
"DIV_CODE",
"DOC_DT_PRV",
"DOC_EN_TTC",
"DOC_ETAT",
"DOC_NUMERO",
"DOC_PIECE",
"DOC_STYPE",
"DOC_TX_DEV",
"DOC_TXRFAC",
"DOC_TYPE",
"NAT_CODE",
"PAY_CODE",
"PCF_CODE",
"PCF_PAYEUR",
"PCF_REMMIN",
"PCF_REMVAL",
"PRJ_CODE",
"REG_CODE",
"REP_CODE",
"SAL_CODE",
"SRV_CODE",
"TAR_CODE";
SQL SELECT DOC_DATE,
DEV_CODE,
DIV_CODE,
DOC_DT_PRV,
DOC_EN_TTC,
DOC_ETAT,
DOC_NUMERO,
DOC_PIECE,
DOC_STYPE,
DOC_TX_DEV,
DOC_TXRFAC,
DOC_TYPE,
NAT_CODE,
PAY_CODE,
PCF_CODE,
PCF_PAYEUR,
PCF_REMMIN,
PCF_REMVAL,
PRJ_CODE,
REG_CODE,
REP_CODE,
SAL_CODE,
SRV_CODE,
TAR_CODE
FROM ICP.dbo.DOCUMENTS;
STORE DOCUMENTS into $(vSaveQVD) DOCUMENTS.QVD(qvd);
DROP TABLE DOCUMENTS;
DOCUMENTS:
LOAD "DOC_DATE",
Month(DOC_DATE) as [Mois],
Year("DOC_DATE") as [Année],
Date("DOC_DATE") as [Date],
If(DOC_TYPE = 'A', 'Achat', if(DOC_TYPE = 'V', 'Ventes')) as [Type de doc],
If(DOC_TYPE = 'V',
if(DOC_STYPE = 'P', 'Pro-forma',
if(DOC_STYPE = 'D', 'Devis',
if(DOC_STYPE = 'C', 'Commandes',
if(DOC_STYPE = 'B', 'Bons de Livraison',
if(DOC_STYPE = 'R', 'Bons de Retour',
if(DOC_STYPE = 'F', 'Factures',
if(DOC_STYPE = '1', 'Factures Financières',
if(DOC_STYPE = 'A', 'Avoirs',
if(DOC_STYPE = '0', 'Avoirs Financiers',
))))))))),
if(DOC_TYPE = 'A',
if(DOC_STYPE = 'D', 'Demandes de Prix',
if(DOC_STYPE = 'C', 'Commandes',
if(DOC_STYPE = 'B', 'Bons de Réceptions',
if(DOC_STYPE = 'R', 'Bons de Retour',
if(DOC_STYPE = 'F', 'Factures',
if(DOC_STYPE = '1', 'Factures Financières',
if(DOC_STYPE = 'A', 'Avoirs',
if(DOC_STYPE = '0', 'Avoirs Financiers')
))))))))
) as [Sous_Type_de_doc],
"DEV_CODE",
"DIV_CODE",
"DOC_DT_PRV",
"DOC_EN_TTC",
"DOC_ETAT",
"DOC_NUMERO",
"DOC_PIECE",
"DOC_STYPE",
"DOC_TX_DEV",
"DOC_TXRFAC",
"DOC_TYPE",
"NAT_CODE",
"PAY_CODE",
"PCF_CODE",
"PCF_PAYEUR",
"PCF_REMMIN",
"PCF_REMVAL",
"PRJ_CODE",
"REG_CODE",
"REP_CODE",
"SAL_CODE",
"SRV_CODE",
"TAR_CODE"
FROM $(vSaveQVD) DOCUMENTS.QVD(qvd);
QUALIFY *;
UNQUALIFY "DOC_NUMERO",
"DEV_CODE",
"DIV_CODE",
"PAY_CODE",
"PCF_CODE",
"REG_CODE",
"REP_CODE",
"SAL_CODE",
"SRV_CODE",
"TAR_CODE";
SQL SELECT "DEV_CODE",
"DOC_DATE",
"DIV_CODE",
"DOC_DT_PRV",
"DOC_EN_TTC",
"DOC_ETAT",
"DOC_NUMERO",
"DOC_PIECE",
"DOC_STYPE",
"DOC_TX_DEV",
"DOC_TXRFAC",
"DOC_TYPE",
"NAT_CODE",
"PAY_CODE",
"PCF_CODE",
"PCF_PAYEUR",
"PCF_REMMIN",
"PCF_REMVAL",
"PRJ_CODE",
"REG_CODE",
"REP_CODE",
"SAL_CODE",
"SRV_CODE",
"TAR_CODE"
From DOCUMENTS;
/*WHERE DOC_TYPE IN ('A','V')*/
DEVISES:
LOAD "DEV_CODE",
"DEV_LIB",
"DEV_MONEY",
"DEV_MONEYS",
"DEV_FORMAT",
"DEV_NB_DEC",
"DEV_SYMBOL",
"DEV_DT_ACT",
"DEV_INCERT",
"DEV_COURS",
"DEV_DTEURO",
"DEV_EURO",
"DEV_DORT",
"DEV_DTMAJ",
"DEV_USRMAJ",
"DEV_NUMMAJ";
SQL SELECT DEV_CODE,
DEV_LIB,
DEV_MONEY,
DEV_MONEYS,
DEV_FORMAT,
DEV_NB_DEC,
DEV_SYMBOL,
DEV_DT_ACT,
DEV_INCERT,
DEV_COURS,
DEV_DTEURO,
DEV_EURO,
DEV_DORT,
DEV_DTMAJ,
DEV_USRMAJ,
DEV_NUMMAJ
FROM ICP.dbo.DEVISES;
STORE DEVISES into $(vSaveQVD) DEVISES.QVD(qvd);
DROP TABLE DEVISES;
DEVISES:
LOAD DEV_CODE,
DEV_LIB,
DEV_MONEY,
DEV_MONEYS,
DEV_FORMAT,
DEV_NB_DEC,
DEV_SYMBOL,
DEV_DT_ACT,
DEV_INCERT,
DEV_COURS,
DEV_DTEURO,
DEV_EURO,
DEV_DORT,
DEV_DTMAJ,
DEV_USRMAJ,
DEV_NUMMAJ
FROM $(vSaveQVD) DEVISES.QVD(qvd);
QUALIFY *;
UNQUALIFY "DEV_CODE";
SQL SELECT "DEV_CODE",
"DEV_LIB",
"DEV_MONEY",
"DEV_MONEYS",
"DEV_FORMAT",
"DEV_NB_DEC",
"DEV_SYMBOL",
"DEV_DT_ACT",
"DEV_INCERT",
"DEV_COURS",
"DEV_DTEURO",
"DEV_EURO",
"DEV_DORT",
"DEV_DTMAJ",
"DEV_USRMAJ",
"DEV_NUMMAJ"
From DEVISES;
Thank you