topic IN clause in QlikView

IN clause

swati_rastogi27 — Mon, 20 Jun 2016 10:28:34 GMT

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?

Re: IN clause

Anonymous — Mon, 20 Jun 2016 10:33:55 GMT

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)

Re: IN clause

trdandamudi — Mon, 20 Jun 2016 10:37:52 GMT

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

Re: IN clause

marcus_sommer — Mon, 20 Jun 2016 10:38:09 GMT

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

Re: IN clause

muthukumar77 — Mon, 20 Jun 2016 10:47:21 GMT

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)

Re: IN clause

swati_rastogi27 — Tue, 21 Jun 2016 11:50:40 GMT

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