Forum Discussion

CG20's avatar
CG20
Regular Visitor
2 years ago

Need help in calculated column

Hi All,

I'm struggling with the calculations below is the data i.e. is Table1 & Table2, they have Many(Table1) to One(Table2) relationship.

 

 

I want that if ID & Country from Table1 has a matching record with the same ID & Country from Table2 then give me the Status column value for that matching record which would be TRUE/FALSE which I have deriverd.

 

I want help from you guys that for Non matching values(N) from Table1 with respect to Table2, Non matching Countries from Table1 should refer to the ALL value from Country column from Table2 & in the output we should get the Status column value of the ALL value of the Country column.

Inshort I want the values from STATUS column when ID&COUNTRY is matching we want STATUS of that particular ID&COUNTRY & for Nonmatching values it should refer to the Country = ALL & should return the Status value of the ALL value to all the nonmatching values of that particular ID

 

I'm trying to create a calculated column as: 

 

Check1 = IF(AND(Postgres[chain_id] = RELATED(Pheonix[ID]), Postgres[country] = RELATED(Pheonix[COUNTRY])), "Y", "N")
Check2 = IF(AND(Postgres[chain_id] = RELATED(Pheonix[ID]), Postgres[Check1] = "Y"), RELATED(Pheonix[Status]), "N")
 
Thanks for reading & helping me advance, eagerly waiting for the replies.

 

 

 

Need Help
Tips and Tricks

2 Replies

  • Arul's avatar
    Arul
    Super User
    2 years ago

    CG20 ,

    Which column serves as the relationship key between the tables? Neither the "country" column nor the "ID" column appears to satisfy a one-to-many relationship between the tables.

    • CG20's avatar
      CG20
      Regular Visitor
      2 years ago

      Hi Arul,

       

      I had created a calculated column which contains concatenation of id&country in both the tables. When you create that you will see a Many(Table1) to One(Table2) relationship.

       

      If you still have any doubts do reply on this post.

       

      Thanks & Regards

      CG20