Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

eduardo_dimperi
Valued Contributor II

Oracle Select with Qlik Sense Exists()

Hi,

I have a table in qlik sense and i need to do a select in a sql table, but only when OID_SYSTEM_AGUA (Qlik Sense) = OID_SYSTEM (SQL).

AGUA:

Load

DISTINCT

OID_SYSTEM_AGUA,

NAME_TYPE_METER_AGUA,

QTDE_MED_AGUA

Resident TOTAL_AGUA;

RS_INDIVIDUAL_BILLING:

LOAD

*

;

SQL

SELECT

OID_SYSTEM

FROM RS_INDIVIDUAL_BILLING

WHERE ???????

7 Replies

Re: Oracle Select with Qlik Sense Exists()

May be where exists under load statement if you are not looking to do this specifically in the sql?

eduardo_dimperi
Valued Contributor II

Re: Oracle Select with Qlik Sense Exists()

Hi Sunny !

But if i use Exists under the Load, my SQL will bring all data and on that exists() will work. I need to extract only the lines that i 'll use.

Re: Oracle Select with Qlik Sense Exists()

Create a variable with all possible value OID_SYSTEM_AGUA and pass the variable to the SQL in that case

eduardo_dimperi
Valued Contributor II

Re: Oracle Select with Qlik Sense Exists()

Yeah I did that once, but the problem is that variable have a maximum number of characters, so for now it works but in a few months not anymore.

eduardo_dimperi
Valued Contributor II

Re: Oracle Select with Qlik Sense Exists()

Right now thats my code

TOTAL_AGUA:

Load

DISTINCT

OID_SYSTEM_AGUA,

NAME_TYPE_METER_AGUA,

QTDE_MED_AGUA

Resident TOTAL_AGUA;

FOR Each oid_system in FieldValueList('OID_SYSTEM_AGUA')

RS_INDIVIDUAL_BILLING:

LOAD

*

;

SQL

SELECT

*

FROM RS_INDIVIDUAL_BILLING

WHERE OID_SYSTEM =$(oid_system) AND DT_FIM >TO_DATE(SYSDATE-180);

NEXT

Re: Oracle Select with Qlik Sense Exists()

Oh really? how many characters can you have in a variable?

eduardo_dimperi
Valued Contributor II

Re: Oracle Select with Qlik Sense Exists()

I can do this too

//******************************************************************************

vWHERE='';

Aux='';

FOR Each condominio in FieldValueList('OID_SYSTEM_AGUA')

Aux='OID_SYSTEM='& $(condominio)&' OR ';

vWHERE=Aux& vWHERE;

NEXT

AUX=LEFT(vWHERE,LEN(vWHERE)-3);

//******************************************************************************

// FOR Each oid_system in FieldValueList('OID_SYSTEM_AGUA')

RS_INDIVIDUAL_BILLING:

LOAD

  *

;

SQL

SELECT

*

FROM RS_INDIVIDUAL_BILLING

WHERE $(AUX) AND DT_FIM >TO_DATE(SYSDATE-180);

Community Browser