Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I have a problem and I don´t know as solve it .. I´ll try explain it ...
I connect with ODBC to my database but my ODBC dont´t allow the sentence LEFT JOIN.
I have two tables join with a field and sql sentence should be:
SELECT F_ECRITUREC.CG_NUM, P_REGLEMENT.R_INTITULE, F_ECRITUREC.EC_MONTANT, F_ECRITUREC.EC_ECHEANCE,
P_REGLEMENT.R_CODE, (F_ECRITUREC.EC_ECHEANCE+P_REGLMENT.R_CODE) AS FechaValor
FROM F_ECRITUREC LEFT JOIN P_REGLEMENT ON F_ECRITUREC.N_REGLEMENT = P_REGLEMENT.CBINDICE
The field "FechaValor" is calculate with fields from both tables ...
How can I do it in QV?
Thanks and best regards
Hi,
You can also use subqueries here, if join is not supported. something like this will give you the same result.
select
F_ECRITUREC.CG_NUM,
(select P_REGLEMENT.R_INTITULE from P_REGLEMENT where F_ECRITUREC.N_REGLEMENT=P_REGLEMENT.CBINDICE) as
R_INTITULE,
F_ECRITUREC.EC_MONTANT,
F_ECRITUREC.EC_ECHEANCE,
(F_ECRITUREC.EC_ECHEANCE+(select P_REGLEMENT.R_CODE from P_REGLEMENT where F_ECRITUREC.N_REGLEMENT=P_REGLEMENT.CBINDICE)) AS FechaValor
FROM F_ECRITUREC;
Right, but row based sub selects could be very slow in some databases.. depending on the amount of records of the outer select.
- Ralf
yeah...that is true and decision on this can only be taken from the person who knows that system.
Hello!!
I tried your code but "Fecha Valor" is not calculated well;
My code is:
F_ECRITUREC:
LOAD *, IF (Apunte_Sentido=0,Apunte_Importe,0) AS Apunte_Debe,
IF (Apunte_Sentido=1,Apunte_Importe,0) AS Apunte_Haber;
SQL SELECT "CG_NUM" as General_Numero,
"CT_NUM" as Tercero_Numero,
(EC_JOUR + JM_DATE)-1 AS Fecha,
"EC_MONTANT" as Apunte_Importe,
"EC_SENS" as Apunte_Sentido,
"JO_NUM" as Diario,
"EC_PIECE" as Apunte_Asiento,
"EC_INTITULE" as Apunte_Descripcion,
"EC_REFPIECE" as Apunte_Factura,
"EC_ECHEANCE" AS Apunte_Vencimiento,
"N_REGLEMENT" AS CodPago,
"EC_LETTRE" AS Compensacion,
"EC_NO" AS NumInterno
FROM F_ECRITUREC ORDER BY JM_DATE ASC;
EcriturecToReglementMap:
MAPPING LOAD CodPago,
Apunte_Vencimiento
RESIDENT F_ECRITUREC;
P_REGLEMENT:
LOAD *,
date((ApplyMap('EcriturecToReglementMap', CBINDICE) + R_CODE),'DD/MM/YYYY') AS FechaValor;
sql SELECT CBINDICE, R_INTITULE, R_CODE FROM P_REGLEMENT;
F_ECRITUREC is a table with many rows and each row has a different due date (EC_ECHEANCE) and different form of payment (N_REGLEMENT). Depending on the method of payment, I have to calculate the "Fecha Valor" adding a number (R_CODE).
P_REGLEMENT is the table with the payment (CBINDICE = N_REGLEMENT).
When you run the script calculates the same value date for each payment, for example, for payment 1 "Fecha Valor" is 29/01/2008 always, for payment is 2/2/ 2008, for payment 3 is 30/01/2008.
I tye to explain better:
The correct is:
N_REGLEMENT EC_ECHEANCE CBINDICE R_CODE FECHA_VALOR
1 22/07/2008 1 1 23/07/2008
1 25/08/2008 1 1 26/08/2008
2 02/03/2008 2 2 04/03/2008
Script does:
N_REGLEMENT EC_ECHEANCE CBINDICE R_CODE FECHA_VALOR
1 03/06/2009 1 1 23/07/2008
I hope I explained well, my English is not very good ....
Thanks in advance for your help !!!
Hi,
The ApplyMap() function needs at least two parameters, being the first the name of the mapping table and the second the "input value" for what it will return the "output value" that is, the second field loaded into the mapping table.
So you may need to add some other field to create a unique key field in both the first field of the mapping table and the second parameter of the ApplyMap() to get the expected result. It seems that CBINDICE is not enough to return a unique value from table F_ECRITUREC or in other words, there is no 1:1 correspodence between F_ECRITUREC.N_REGLEMENT and P_REGLEMENT.CBINDICE
Hope that makes sense.
Miguel
Gracias Miguel!
Tal y como están los datos no puedo conseguir que haya una única clave entre las dos tablas ya que F_ECRITUREC guarda el indice de P_REGLEMENT y ésta es la que tiene el valor que hay que sumar pero, muchos registros de F_ECRITUREC pueden tener el mismo valor (F_ECRITUREC es una tabla de puntes contables y P_REGLEMENT es la tabla de formas de pago).
Con un simple LEFT JOIN estaria solucionado pero como ya he explicado anteriormente el ODBC de la aplicación no lo permite ...
Voy a ver si puedo hacerlo de otra forma pero de todas formas ....
Muchisimas gracias por la ayuda que me habeis prestado !!!
Thanks everybody!
Gracias Miguel!
Tal y como están los datos no puedo conseguir que haya una única clave entre las dos tablas ya que F_ECRITUREC guarda el indice de P_REGLEMENT y ésta es la que tiene el valor que hay que sumar pero, muchos registros de F_ECRITUREC pueden tener el mismo valor (F_ECRITUREC es una tabla de puntes contables y P_REGLEMENT es la tabla de formas de pago).
Con un simple LEFT JOIN estaria solucionado pero como ya he explicado anteriormente el ODBC de la aplicación no lo permite ...
Voy a ver si puedo hacerlo de otra forma pero de todas formas ....
Muchisimas gracias por la ayuda que me habeis prestado !!!
Thanks everybody!
Finally, I found solution:
F_ECRITUREC:
LOAD *, IF (Apunte_Sentido=0,Apunte_Importe,0) AS Apunte_Debe,
IF (Apunte_Sentido=1,Apunte_Importe,0) AS Apunte_Haber,
iF (Codigo_Nat>= 1 and Codigo_Nat <=5, General_Numero, null()) as Cuenta_Tesoreria;
SQL SELECT "F_ECRITUREC"."CG_NUM" as General_Numero,
"CT_NUM" as Tercero_Numero,
(EC_JOUR + JM_DATE)-1 AS Fecha,
"EC_MONTANT" as Apunte_Importe,
"EC_SENS" as Apunte_Sentido,
"JO_NUM" as Diario,
"EC_PIECE" as Apunte_Asiento,
"EC_INTITULE" as Apunte_Descripcion,
"EC_REFPIECE" as Apunte_Factura,
"EC_ECHEANCE" AS Apunte_Vencimiento,
"N_REGLEMENT" AS CodPago,
"EC_LETTRE" AS Compensacion,
"EC_NO" AS NumInterno,
"N_NATURE" AS Codigo_Nat
FROM F_ECRITUREC INNER JOIN F_COMPTEG ON F_ECRITUREC.CG_NUM = F_COMPTEG.CG_NUM ORDER BY JM_DATE ASC;
LEFT JOIN LOAD *;
SQL SELECT "R_INTITULE" AS FormaPago,
"CBINDICE" as CodPago,
"R_Code" as Aplazamiento
FROM P_REGLEMENT;
Apuntes:
LOAD General_Numero,Tercero_Numero, Fecha,Apunte_Importe,Apunte_Sentido,Diario,Apunte_Asiento, Apunte_Descripcion,Apunte_Factura,Apunte_Vencimiento,
CodPago,Compensacion, NumInterno, Codigo_Nat,FormaPago,Aplazamiento,Apunte_Debe,Apunte_Haber,Cuenta_Tesoreria,
if(isnull(Aplazamiento),date(Apunte_Vencimiento,'DD-MM-YYYY'),date(Apunte_Vencimiento+Aplazamiento,'DD-MM-YYYY')) as FechaValor
resident F_ECRITUREC;
drop table F_ECRITUREC;
I hope it is useful..
thanks everyone
I encountered the similar issue but with little bit of tweaking ODBC left join worked.
The main point that I noticed is use of alias instead of absolute path. When I was referring to the absolute path (ie database.tablename) the statement worked perfectly fine in database but ODBC connection in QLikview threw me an error .(DB2 is my backend )
SELECT
Table1.Field1,
Table2.Field2
FROM Database1.Table1 LEFT OUTER JOIN Database1.Table2
ON Table1.Field1 = Table2.Field1
WITH UR ;
So I modifed the query to use an alias and wrote the following query. It worked with out any error through ODBC connection.
//Example code
//Field 3 exist in only one of the table
[Example]:
Load
Field1,
Field2,
Field3;
SQL SELECT
Field3,
A.Field1,
B.Field2
FROM Database1.Table1 as A LEFT OUTER JOIN Database1.Table2 as B
ON A.Field1 = B.Field1
WITH UR ;
Note: Aliasing is not required in case the field exists in only one table
NB: Please note that the Query is just to illustrate the idea. Please check the syntax according to the database that you are using