Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

eduardo_dimperi
Valued Contributor II

Using one table to filter another

Hi evereybody,

I need some help, cause i want to select some data from RS_INDIVIDUAL_BILLING where OID_METER are in [lib://Dados/MI/Leituras/Consumo/Condominios/Carga_Consolidada_20170424.qvd].

How can i do that?

LOAD

OID_METER

FROM

[lib://Dados/MI/Leituras/Consumo/Condominios/Carga_Consolidada_20170424.qvd]

RS_INDIVIDUAL_BILLING:

LOAD

*

;

SQL

SELECT

*

FROM RS_INDIVIDUAL_BILLING

WHERE MONTH1>='20/04/2017' AND OID_METER = ??????;

Thank you

1 Solution

Accepted Solutions
MVP
MVP

Re: Using one table to filter another

LOAD OID_METER

FROM [lib://Dados/MI/Leituras/Consumo/Condominios/Carga_Consolidada_20170424.qvd]


RS_INDIVIDUAL_BILLING:

LOAD *

where exists (OID_METER)

;

SQL

SELECT *

FROM RS_INDIVIDUAL_BILLING

WHERE MONTH1>='20/04/2017';

8 Replies
MVP
MVP

Re: Using one table to filter another

LOAD OID_METER

FROM [lib://Dados/MI/Leituras/Consumo/Condominios/Carga_Consolidada_20170424.qvd]


RS_INDIVIDUAL_BILLING:

LOAD *

where exists (OID_METER)

;

SQL

SELECT *

FROM RS_INDIVIDUAL_BILLING

WHERE MONTH1>='20/04/2017';

atkinsow
Valued Contributor II

Re: Using one table to filter another

I don't believe you can do it directly in your SQL statement.  But here is one option:

LOAD

OID_METER

FROM

[lib://Dados/MI/Leituras/Consumo/Condominios/Carga_Consolidada_20170424.qvd]

RS_INDIVIDUAL_BILLING:

LOAD

*,

where exists(OID_METER)

;

SQL

SELECT

*

FROM RS_INDIVIDUAL_BILLING

WHERE MONTH1>='20/04/2017';

eduardo_dimperi
Valued Contributor II

Re: Using one table to filter another

Hi Massimo,

but how the QS will distinguish OID_METER from Carga_Consolidada_20170424 that OID_METER from RS_INDIVIDUAL_BILLING? Cause both have this Field.

eduardo_dimperi
Valued Contributor II

Re: Using one table to filter another

Hi Wallo,

I have the same question about that from Massimo answer.

How the QS will distinguish OID_METER from Carga_Consolidada_20170424 that OID_METER from RS_INDIVIDUAL_BILLING? Cause both have this Field.

eduardo_dimperi
Valued Contributor II

Re: Using one table to filter another

Ahh i understand now, i read the help of QS:

"    Returns -1 (True) if the value of the field in the current record already exists in any previously read record that contains this field."

MVP
MVP

Re: Using one table to filter another

1)

Qlik loads the values of OID_METER from qvd, Carga_Consolidada_20170424



2)

Preceding Load

Qlik will load the values of OID_METER from RS_INDIVIDUAL_BILLING (SQL .... FROM ....WHERE).

You get all OID_METER values from the database but in the LOAD (LOAD * where exists(OID_METER)) Qlik will only get the value of OID_METER already loaded in step 1

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/InterRecordFunctions/E...

eduardo_dimperi
Valued Contributor II

Re: Using one table to filter another

Wallo, i read the qlik sense help to understand the answer and now i think that did it.

"    Returns -1 (True) if the value of the field in the current record already exists in any previously read record that contains this field."

MVP
MVP

Re: Using one table to filter another

Community Browser