Calcultation View

[former_member254442]

Hi All, I am trying to model a  calculation view as per attached joint but not getting the desired result . Left table is AFVC & Right table is AFKO . AUFPL is common field . For one record in right table AFKO, there are multiple corresponding records in left table AFVC and also, there are some records in left table AFVC which are not available in right table AFKO ( due to filter put for field AUART in table AFKO ,however same field is not available in table AFVC to put the same filter )  . I need to populate all records from left table but without those records which are not available in right table . Thanking you in advance . BR,

[anindya_bose · Accepted solution]

I feel , you need Inner Join in that case unless I misunderstood your requirement .

Regards

Anindya

[former_member254442 · Accepted solution]

Thanks for the reponse . In case of inner joint for this scenario , i wil get only common records . However, my scenario is different as explained below .

say in table AFKO, value of field AUFPL is  1  .  This value repeates in table AFVC ( in this table ,let us take 2 field. One is AUFPL & another is APLZL ) One combination in table is AUFPL = 1, APLZL = a ,another combination in table AFVC is , AUFPL = 1 , APLZL = b & so on ) . Along with these values , in table AFVC there are other records example, AUFPL = 101 , APLZL = x ( these values are not available in table AFKO , So i do not need this record ) . In my report , only there will be 2 records ( with value of one record is AUFPL=1 ,APLZL =a and another record AUFPL= 1 ,APLZL= b ) even though there are 3 records in table AFVC.

Please suggest .

[anindya_bose · Accepted solution]

You will filter AVFC with APLZL in ( a,b etc )  and join AFKO and AFVC with AUFPL.

If AFKO has one entry and AVFC has two after filtering , in the result set you get 2 records.

Fields from AFKO would be repeated for both the records.

Regards

Anindya

[Former Member · Accepted solution]

Hi Pranjal,

As suggested by Anindya, Inner join with cardinality of n:1 should work for you. It will return the 2 records - ( with value of one record is AUFPL=1 ,APLZL =a and another record AUFPL= 1 ,APLZL= b ) even though there are 3 records in table AFVC)..

Thanks,

Nithya V

[former_member254442 · Accepted solution]

Hi Bose,

How do I put filter expression as I have more than hundred thousand values for APLZL field .

[anindya_bose · Accepted solution]

Take AVFC in a Projection and apply filter in Projection . Use "List of Values"

Take AFKO in another projection.

Join these two projections with a join node .

Regards

Anindya

[Former Member · Accepted solution]

Hi Anindya/Pranjal,

May be Im missing the point here, but can you explain why you need to filter values in AFVC?

Inner join with cardinality of n:1  will return the 2 records - (  AUFPL=1 ,APLZL =a and  AUFPL= 1 ,APLZL= b ) even though there are 3 records in table AFVC.

I dont see why we need to filter, as the inner join with n:1 may work fine in this case.

Thanks!

[anindya_bose · Accepted solution]

I still have some doubt about the requirement .  What I assumed is , he needs  APLZL values  a and b  and some other fields associated with it . So, suggested to use a filter.

However, after all the conversation I feel his requirement could be like this .

1) Take all AUFPL from AVFC  where APLZL in ( a, b, c ..... ) .

2) Select all records from AFKO where AUFPL in list in step 1 .

Pranjal, can you please clear your requirement once again and just explain the table structure ?

[former_member254442 · Accepted solution]

Hi,

Yes ,inner joint with n:1 cardinality works . Thanks all of you .

[anindya_bose · Accepted solution]

Even if you keep Cardinality blank , it would work .  It's inner join which makes it work .

However, setting right cardinality is recommended .

Regards

Anindya

[Former Member]

As you have mentioned that filter(AUART) is there on table AFKO

Left Table : AFKO

Right Table : AFVC

Join Type : Inner/Referential

Cardinality : 1:N

This will fetch you mulitple AFVC- AUFPL/APLZL for the corresponding AFKO-AUFPL