topic Re: How to use exists() in QlikView

How to use exists()

ananyaghosh — Sun, 15 Feb 2015 09:16:14 GMT

Hello Firends,

I have created the following scripts, but does not get any reults:

LOAD * INLINE [

ids

10,

];

table2:

LOAD * INLINE [

id, flag

1, 1

2, 1

3, 1

4, 1

5, 1

6, 1

7, 2

7, 1

7, 3

];

QUALIFY *;

table3:

LOAD * resident table2 where exists(id);

Now i want that table 1 and table1 's common values will be loaded.

What should I do now?

Re: How to use exists()

Sun, 15 Feb 2015 09:46:26 GMT

Hi,

You can try like this without loading a table3:

table2:

LOAD * INLINE [

id, flag

1, 1

2, 1

3, 1

4, 1

5, 1

6, 1

7, 2

7, 1

7, 3

]

Where Exists(ids, id);

Depending on your need and scenario, you may actually want to join both of tables, or link them by renaming id to ids in table2 (in which case you won't need to provide a second parameter for the exists function: "where exists(ids)").

Re: How to use exists()

terezagr — Sun, 15 Feb 2015 11:00:41 GMT

First of all you have an error in your 1 Load * Inline syntax - you have comas after 6 and 10 without having these enclosed in "" (in case the comas are supposed to be there). In case the comas are not supposed to be there, delete them.

LOAD * INLINE [

ids

"6,"

"10,"

];

or if comas are not supposed to be there:

LOAD * INLINE [

ids

];

Also, if you want to link table 1 and table2 you need to rename ids to id in your table 1.

Use the script below to do so:

LOAD * INLINE [

];

table2:

LOAD * INLINE [

id, flag

1, 1

2, 1

3, 1

4, 1

5, 1

6, 1

7, 2

7, 1

7, 3

];

QUALIFY *;

table3:

LOAD * resident table2 where exists(id);

You should now be presented with this:

Re: How to use exists()

ananyaghosh — Wed, 25 Mar 2015 13:02:42 GMT

If I does not use QUALIFY *; what will happen that means what is the use of QUALIFY *; here?

Re: How to use exists()

Wed, 25 Mar 2015 13:11:02 GMT

If you will not use Qualify then you need to alise all fields from new table to avoid that & also to avoid synthetic key ..So better to use Qualify ..

Re: How to use exists()

saradhi_it — Wed, 08 Apr 2015 17:36:55 GMT

Hi Sandip,

the qualify and unqualify are important functions in qlikview the important thing while you are using qualify in the syntax while you must use unqualify at the end of the script else the database crash occurs...