Join two tables in script with ODBC

[#1]

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

[rbecher]

Hi,

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

- Ralf

[Miguel_Angel_Baeyens]

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

[rbecher]

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

[#5]

Ok!!

Thank you very much !!

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

[#6]

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(+)

[#7]

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

.

[rbecher]

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

[Miguel_Angel_Baeyens]

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

[rbecher]

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