Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

The exists function syntax

There are two tables Main Data and Aircraft Types. Their only common field is %Aircraft type Id, which means "type of aircraft". Main data does not contain all the types listed in aircraft types. We want to load only the types which exist in both tables.

This is how it can be done in the script editor -

[Aircraft Types]:

LOAD AC_TYPEID as [%Aircraft Type ID],

     AC_GROUP as [%Aircraft Group Type],

--------more fields here.

FROM

[..\Data Files\CSVs\Aircraft_Base_File.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq)

Where Exists([%Aircraft Type ID], AC_TYPEID);


My book says that WHERE EXISTS is actually checking if the %Aircraft Type ID's in Aircraft types exist in main data or not. I go to Main Data tab and see a field %Aircraft Type ID. So, I know that we are comparing [%Aircraft Type ID] of Main with AC_TYPEID of Types.

This syntax is unintuitive. Do I have to search the script all the time to find out which table a field belongs to ?

I was hoping for syntax like this -

Where Exists([Main Data].[%Aircraft Type ID], [Aircraft Types].[AC_TYPEID]);

OR even this -

Where [Aircraft Types].[AC_TYPEID]

EXISTS IN([Main Data].[%Aircraft Type ID]);



2 Replies
swuehl
MVP
MVP

You can prefix the field names with their table names, if you want and if that's what you are asking for here.

Please have a look at the QUALIFY statement in the help, which makes such things easier (but key fields can't be prefixed like this anyway).

its_anandrjs

Update Sorry i forget some think now updated please check now.

I believe you are not selecting correct field see the small example for the Where Exists

Table1:

Load * Inline

[

A

1

2

3

4

5

6

];

NoConcatenate

Table2:

Load * Inline

[

AA

1

2

3

4

5

7

]Where  Exists(A,AA);

In this code you are get only Table1 Data and also if it is available in the Table2 also with field name AA then you get 1,2,3,4,5 which is common in both the tables.

Regards