topic Join two tables in script with ODBC in QlikView

Join two tables in script with ODBC

Thu, 26 Jan 2012 10:30:45 GMT

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

Join two tables in script with ODBC

rbecher — Thu, 26 Jan 2012 10:43:14 GMT

Hi,

this is a bit strange. Which database vendor and which ODBC driver you are using?

- Ralf

Re: Join two tables in script with ODBC

Miguel_Angel_Baeyens — Thu, 26 Jan 2012 10:47:49 GMT

Hi,

Load each data base table in a separate QlikView table. Then do the LEFT JOIN or a MAPPING LOAD in QlikView to get the date field. It should look like this, I haven't tested it though. The ApplyMap() line in the example above will read "return the EC_ECHEANCE field from F_Ecriturec table when value in CBINDICE is equal to the value in N_REGLEMENT, and sum R_CODE to that value".

F_Ecriturec:
LOAD *;
SQL SELECT N_REGLEMENT, CG_NUM, EC_MONTANT, EC_ECHEANCE
FROM F_ECRITUREC;
EcriturecToReglementMap:
MAPPING LOAD N_REGLEMENT,
     EC_ECHEANCE
RESIDENT F_Ecriturec;
P_Reglement:
LOAD *,
     ApplyMap('EcriturecToReglementMap', CBINDICE) + R_CODE AS FechaValor
SELECT CBINDICE, R_INTITULE, R_CODE
FROM P_REGLEMENT;

Hope that helps.

Miguel

Re: Join two tables in script with ODBC

rbecher — Thu, 26 Jan 2012 10:57:27 GMT

Could be impossible or take a very longtime to load all data from both tables if it's a big amount of records. This should work on a database level..

- Ralf

Re: Join two tables in script with ODBC

Thu, 26 Jan 2012 11:17:33 GMT

Ok!!

Thank you very much !!

I'll try your solution as soon as possible and I will tell you if it worked!!

Re: Join two tables in script with ODBC

Thu, 26 Jan 2012 11:25:06 GMT

Hi,

this is very strage, Left join should work.

anyways, To do it in Qlikview, you can follow Miguel's suggestion.

If it is oracle may be you can try this because keyword left join is not supported in older versions.

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,P_REGLEMENT

where F_ECRITUREC.N_REGLEMENT = P_REGLEMENT.CBINDICE(+)

Join two tables in script with ODBC

Thu, 26 Jan 2012 11:33:51 GMT

Ok Ralf!!

The table F_CRITUREC may have many records, the other table only 50.

Database can be two types:

- SQL (no problem)

- software provider specifies (with ODBC)

When it is not SQL, I only can access to it from ODBC and, although this may seem strange, SQL sentences only support INNER JOIN and WHERE campo1 = campo2 (instead of inner join sentence).

I´ll try Miguel´s solution and I hope it works!!!!

Regards

Join two tables in script with ODBC

rbecher — Thu, 26 Jan 2012 11:42:24 GMT

There is another left join DB level solution (w/o join) for this:

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 INNER JOIN P_REGLEMENT ON F_ECRITUREC.N_REGLEMENT = P_REGLEMENT.CBINDICE

UNION ALL

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 AS FechaValor

FROM F_ECRITUREC

WHERE NOT EXISTS (SELECT * FROM P_REGLEMENT WHERE P_REGLEMENT.CBINDICE=F_ECRITUREC.N_REGLEMENT)

- Ralf

Re: Join two tables in script with ODBC

Miguel_Angel_Baeyens — Thu, 26 Jan 2012 11:44:25 GMT

Ralf,

I totally agree that the more work the database does, the simpler the load will be. I'm basing my answer on the assumption that, actually, the ODBC is not able to make the RDBM perform LEFT JOINs. JOINs work really poor in QlikView even using numeric key values and a super high density of data, not to mention the disk usage increment (to save, at the end of the day, the same number of rows).

But there are indeed cases where the JOINs must be done in QlikView, and hence my answer.

Kind regards.

Miguel

Re: Join two tables in script with ODBC

rbecher — Thu, 26 Jan 2012 11:50:03 GMT

Hi Miguel,

I think your mapping based solution in QV works perfect and is fast for lookup tables with a few records.

I just wanted to show that a LEFT JOIN can be made also with INNER JOIN + UNION ALL at DB level.

We should always think about to use the backend DB power..

Cheers!

Ralf

Re: Join two tables in script with ODBC

Thu, 26 Jan 2012 11:50:21 GMT

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;

Re: Join two tables in script with ODBC

rbecher — Thu, 26 Jan 2012 11:52:56 GMT

Right, but row based sub selects could be very slow in some databases.. depending on the amount of records of the outer select.

- Ralf

Re: Join two tables in script with ODBC

Thu, 26 Jan 2012 11:55:11 GMT

yeah...that is true and decision on this can only be taken from the person who knows that system.

Re: Join two tables in script with ODBC

Tue, 31 Jan 2012 08:37:43 GMT

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

1 03/06/2009 1 1 04/06/2009

2 02/03/2008 2 2 04/03/2008

2 05/05/2008 2 2 07/05/2008

Script does:

N_REGLEMENT EC_ECHEANCE CBINDICE R_CODE FECHA_VALOR

1 22/07/2008 1 1 23/07/2008

1 25/08/2008 1 1 23/07/2008

1 03/06/2009 1 1 23/07/2008

2 02/03/2008 2 2 04/03/2008

2 05/05/2008 2 2 04/03/2008

I hope I explained well, my English is not very good ....

Thanks in advance for your help !!!

M.C.

Re: Join two tables in script with ODBC

Miguel_Angel_Baeyens — Tue, 31 Jan 2012 14:51:18 GMT

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

Re: Join two tables in script with ODBC

Fri, 03 Feb 2012 07:58:48 GMT

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!

Re: Join two tables in script with ODBC

Fri, 03 Feb 2012 07:59:56 GMT

Gracias Miguel!

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!

Re: Join two tables in script with ODBC

Tue, 08 May 2012 08:14:34 GMT

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

Re: Join two tables in script with ODBC

Wed, 02 Jan 2013 18:53:11 GMT

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