Skip to Content

Calculation View - Filtering Table1 where not in Table2

Hello,

We have a requeriment where we need to filter Table1 with those records (based on the Account) that are not in Table2.

Here is an example

Table1 Table2 Result Account Value A001 100 A002 120 A003 210 A004 150 Account A002 A004 Account Value A001 100 A003 210

How do you think we can accomplish this? Is there a way to do it in a the Calculation View in Graphical mode?

Thanks, Fede

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jul 05, 2016 at 03:27 AM

    hi

    please check as follows

    Use a left outer join then filter for null values on the column account_1


    Capture1.PNG (14.9 kB)
    Capture2.PNG (7.6 kB)
    Capture3.PNG (4.4 kB)
    Capture4.PNG (7.5 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 05, 2016 at 03:56 AM

    Hi Federico A Galban,

    There is no straight forward approach for this scenario in graphical calculation view. But you can do this with some work around.

    • Join both the tables using join node, consider Table 1 as left table and Table 2 as right table and make the join type as Left Outer Join
    • Put one projection node above that join node and write a filter expression to filter only NULL values from Table2.Account column, so that you can achieve what you need.

    Hope this helps you,

    Regards,

    HarikrishnanS

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 22, 2016 at 09:28 AM

    Hi ,

    I am using graphical model for above scenario and i tried with script based CV , it's giving better performance than graphical view.


    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 22, 2016 at 04:40 PM

    Hello

    Both Arun Kumar's and Narendra's approaches can be implemented. Even I experienced good performance by using scripted calculation view using NOT IN operator than Graphical view. There were 4 tables involved in my scenario and the tables were huge as well.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 23, 2016 at 08:23 PM

    You can also check the alternatives as mentioned in this blog :

    Thinking in HANA - Part 1: Set Operators

    Regards,

    Krishna Tangudu

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.