IN clause

[swati_rastogi27]

Hi all ,

I have a requirement to convert sql code to qlikview.

SELECT <all columns> FROM LOANS WHERE LOAN_NUMBER IN

( SELECT DISTINCT LOAN_NUMBER FROM REFERENCE_TABLE WHERE <some_condition>)

I have stored all LOANS data into a QVD : Loans.qvd

Stored all reference data into reference table REF_TABLE

Now I want to do a resident load:

LOAD <all columns> RESIDENT (Loans.qvd) WHERE

LOAN_NUMBER IN

( LOAN DISTINCT LOAN_NUMBER RESIDENT REF_TABLE)

I am not able to achieve this. Can someone help?

In short, how can we use a resident load sub query into another resident load query?

[Anonymous]

try to load ref table first and then loadn table

Ref:

load *

from RESIDENT REF_TABLE

Loan:

load *

from Loan.qvd

where exists (LOAN_NUMBER)

[trdandamudi]

May be as below:

First load the REF_TABLE and then Loans.qvd :

Ref_Table_Data:

Load Distinct LOAN_NUMBER

from REF_Table.qvd;

Loans_Data:

Load *

From Loans.qvd

where Exists(LOAN_NUMBER);

Hope this helps....

[marcus_sommer]

In qlik you would need the following logic:

t0:

Load DISTINCT LOAN_NUMBER from REFERENCE_TABLE WHERE <some_condition>;

t1:

Load * From LOANS WHERE exists(LOAN_NUMBER);

drop table t0;

- Marcus

[muthukumar77]

Hi,

LOAN_NUMBER:
Load distinct LOAN_NUMBER from REFERENCE_TABLE WHERE <some_condition>

LOAD LOAN_NUMBER1, * FROM LOANS WHERE LOAN_NUMBER exists (LOAN_NUMBER,LOAN_NUMBER1)

[swati_rastogi27 · Accepted solution]

Hi all ,

Exists did not help , so I created a LOANS_EXCLUDES static table and INNER JOINED it with main table.

That solved the purpose