topic Re: Using Data loaded from a file in IN / MATCH clause in QlikView

Using Data loaded from a file in IN / MATCH clause

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Using Data loaded from a file in IN / MATCH clause

sunny_talwar — Fri, 03 Aug 2018 14:28:45 GMT

Try this

[users]:

LOAD

FROM table_name

WHERE Exists(Y, A);

Re: Using Data loaded from a file in IN / MATCH clause

Anonymous — Mon, 06 Aug 2018 13:23:34 GMT

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)

Re: Using Data loaded from a file in IN / MATCH clause

sunny_talwar — Mon, 06 Aug 2018 15:43:44 GMT

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?

Re: Using Data loaded from a file in IN / MATCH clause

Anonymous — Mon, 06 Aug 2018 16:02:22 GMT

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.

Re: Using Data loaded from a file in IN / MATCH clause

BalaBhaskar_Qlik — Mon, 06 Aug 2018 16:10:22 GMT

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

FROM table_name;

ABC:

Mapping load

Resident [var_name];

Temp:

Load

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

Resident [users];

Drop table [users];

Re: Using Data loaded from a file in IN / MATCH clause

sunny_talwar — Mon, 06 Aug 2018 16:34:55 GMT

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