Use of the EXISTS formula - have I understood this right?

[datanibbler]

Hi,

I am just unsure whether I have understood this right. I know how to technically use the EXISTS() function, but there is a limitation in the interpretation of its results and I wonder whether I have got these right:

I have two tables, one ([tab_A]) has a rel. small nr. of items and the other ([tab_B]) has a very big nr. of items - some of these are associated, like >> five items from [tab_B] are packed into one item in [tab_A]l <<.

To find all the items in [tab_B] which are associated to any items in [tab_A], I use the EXISTS() option in a LOAD - that seems to work fine.

>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

LOAD ... FROM [tab_B] WHERE EXISTS();

>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

<=> But it does not mean that all the items from [tab_A] do have any items in [tab_B] associated, does it?

=>  To find out how many items in [tab_A] do have any items in [tab_B] associated, I have to do the same thing the other way round, no?

>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<

LOAD ... FROM [tab_A] WHERE EXISTS();

>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<

Thanks a lot!

Best regards,

DataNibbler

[vinieme12]

to find out how many rows do not exist in the previous table you need to Add NOT before Exist

You may need to use KEEP statement here

[prieper]

You may do a JOIN betw both tables and the filter for the Items carrying no data.

Else you may do a comparison on the pointertables, which might be more performant with higher amount of data, with FIELDVALUELIST()

Peter

[datanibbler]

Hi,

@ vineeth

That option is not in my help_file - was it included in a later version of QV (I have 11.20.12664.0 SR9)

@ Peter

That sounds interesting. I have never yet worked with the pointertables rather than with the actual data, but I'd like to learn more about that. Well, let's see ...

[vinieme12 · Accepted solution]

Not sure when

You can use a combination of KEEP and Exist as below to answer your queries

1)

The below will return rows that have a match in Table A

A:  // unique ID's

LOAD * INLINE [

    ID, colors

    1, violet

    2, indigo

    3, blue

    4, green

    5, yellow

    6, orange

];

Left Keep

B:                  // 11 Rows  does not include ID's 3 and 6  ,ID 7 does not exist in table A

LOAD * INLINE [

    ID, COLORS

    1, violet

        1, violet

        1, violet

    2, indigo

  2, indigo

  2, indigo

    4, green

    5, yellow

  5, yellow

    7, red

    7, red

]

WHERE Exists (ID);

2)

The below will return rows that do not exist in Table A

A:   // unique ID's

LOAD * INLINE [

    ID, colors

    1, violet

    2, indigo

    3, blue

    4, green

    5, yellow

    6, orange

];

Right Keep

B:                   // 11 Rows  does not include ID's 3 and 6  ,ID 7 does not exist in table A

LOAD * INLINE [

    ID, COLORS

    1, violet

        1, violet

        1, violet

    2, indigo

  2, indigo

  2, indigo

    4, green

    5, yellow

  5, yellow

    7, red

    7, red

]

WHERE NOT Exists (ID);

[swuehl]

Both your statements using Exists() are not valid syntax, since Exists() require at least one argument, a field name.

To help you with your question, please post a more specific sample of your script, including some sample records that show how your items look like and how your tables are related.

Also don't forget to post your required results.

[datanibbler]

Thanks a lot for all the answers!

@ swuehl

Actually I did know that, it was just supposed to be an example 😉

@ vineeth

That looks good, i do understand what you're doing and it's quite similar to my own approach - but instead of first using EXISTS() and then using NOT(EXISTS()), I can just as well do as I'm currently doing. It should return the same results - I will try just to test the validity of my own method.

Thanks a lot!

Best regards,

DataNibbler

P.S.: Sorry, that was a misunderstanding, vineeth - I thought you meant a >>NOT before << option of some sort which I didn't know - I do know the >>NOT(EXISTS())<< option 😉