Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Is it possible to load table from database connection based on the values from resident table

I have 'Table1' as below

Table1:

LOAD ID, Name;

LIB CONNECT TO <My Database Connection>;

SELECT ID, NAME FROM <MyTable1>;

I want to load another table from my database connection like below:

Table2:

LIB CONNECT TO <My Database Connection>;

SELECT ID, NAME, ADDRESS, PHONE FROM <MyTable2>

WHERE ID=(All ID values in resident Table1);


Is there a way to achieve this?

3 Replies
hector_munoz_in
Valued Contributor

Re: Is it possible to load table from database connection based on the values from resident table

Hi Vimarsh;

Look at this code:

// Database conexion (MySQL)

$(vsConexionMYSQL);

// Reading tables to download (to QVD file) from metadata table in MySQL

TABLAS_MYSQL_DESCARGAR:

SQL SELECT table_name AS Tabla

FROM information_schema.`tables`

WHERE table_schema = "XXXXXXXX" AND

  table_type = "BASE TABLE"; // Solo tablas (se excluyen las vistas)

// Number of tables to download

LET vsNumTablasAQVD = NoOfRows('TABLAS_MYSQL_DESCARGAR');

// Tables iteration

FOR vsContador = 0 TO $(vsNumTablasAQVD) - 1

  // Iteration table

  LET vsTablaIteracion = Peek('Tabla', $(vsContador), 'TABLAS_MYSQL_DESCARGAR');

  // Load iteration table

  $(vsTablaIteracion):

  LOAD '$(cEMPRESA_VALORIZA)' AS EMPRESA,

  *;

  SQL SELECT *

  FROM $(vsBASEDATOS).$(vsTablaIteracion);

  // Download iteration table

  STORE * FROM $(vsTablaIteracion) INTO $(vRAW_DATA_MYSQL_QVD_DIR)\$(vsTablaIteracion)_$(cEMPRESA_VALORIZA).qvd;

  // Drop of the iteration table

  DROP TABLE $(vsTablaIteracion);

NEXT

// Drop of the master table

DROP TABLE TABLAS_MYSQL_DESCARGAR;

I hope it serves!

Regards,

H

Not applicable

Re: Is it possible to load table from database connection based on the values from resident table

Hello Hector,

Thank you for this script. It really helped.

Best,

Vimarsh Saxena

kaanerisen
Contributor III

Re: Is it possible to load table from database connection based on the values from resident table

Try this:

I have 'Table1' as below

Table1:

LOAD ID, Name;

LIB CONNECT TO <My Database Connection>;

SELECT ID, NAME FROM <MyTable1>;

I want to load another table from my database connection like below:

Table2:

LIB CONNECT TO <My Database Connection>;

LOAD *

WHERE EXISTS (ID,ID);

SELECT ID, NAME, ADDRESS, PHONE FROM <MyTable2>;