Using one table to filter another

[eduardo_dimperio]

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

[maxgro · Accepted solution]

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';

[Anonymous]

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_dimperio]

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_dimperio]

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_dimperio]

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."

[maxgro]

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_dimperio]

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."

[maxgro]

If you got the answer

Qlik Community Tip: Marking Replies as Correct or Helpful