Using Data loaded from a file in IN / MATCH clause

[Anonymous]

Hi,

I am loading data from a CSV using the following command:

[var_name]:
LOAD
    @1 as X,
    @2 as Y
FROM [source]
(txt, codepage is 28591, no labels, delimiter is ';', msq);

Upon debugging I found out that the data is getting loaded succcessfully.

Now, i want to use one of these columns (Y), in the IN clause or QLIK equivalent MATCH clause.

[users]:
LOAD
     A,
     B
FROM table_name 
WHERE A IN (Y);

How do i write the WHERE clause in this case. Assume that Y contains more than 500 entries and I cannot write each of them individually in MATCH clause (match(field, value1, value2, ...)).

Thanks in advance.

[sunny_talwar · Accepted solution]

Try this

[users]:

LOAD

    A,

    B

FROM table_name

WHERE Exists(Y, A);

[Anonymous]

1) Shouldn't it be Exists(Y, B)?

2) How do tell Qlik that Y is a column in [var-name]? It is giving me error if I use Exists("var-name".Y, B)

[sunny_talwar]

1) But you said you wanted to check Where A in (Y)?

Did that change?

2) Y is a var-name? Not sure what that means?

[Anonymous]

1) No that didn't change.

2) Y is not a var-name. What I meant is:

[var_name]: 

LOAD 

    @1 as X, 

    @2 as Y 

FROM [source] 

(txt, codepage is 28591, no labels, delimiter is ';', msq); 

Y exists in this [var-name].

So how do I use it in EXISTS clause of another table script.

[balabhaskarqlik]

May be by using of Mapping:

[var_name]: 

LOAD 

    @1 as X, 

    @2 as Y 

FROM [source] 

(txt, codepage is 28591, no labels, delimiter is ';', msq);

Noconcatenate

[users]: 

    LOAD 

         A, 

         B 

    FROM table_name;

ABC:

Mapping load

x,

y

Resident [var_name];

Temp:

Load

    Applymap('ABC',Y,'No Match') as A,

    B

Resident [users];

Drop table [users];

[sunny_talwar]

Exists functions works with the field name from any table loaded before it's use in the Exists function... if you have loaded Y only in var-name table... you can safely use Where Exists(Y, A)... because it will check for values in Y field that matches with A....

Does this make sense? You can read about more here

Exists - script function