Forum Discussion

rohitMe's avatar
rohitMe
Advocate I
8 years ago
Solved

lookup between tables

Hi all,

 

I have customer details and I want to look up whether a customer is listed twice. I have two tables that look as shown below:

 

Table 1Table 2

 

I want to identify whether the customer is listed twice using the customer mobile column in both the tables and create a new column in Table 1 listing the "CALL ID" from Table 2 of the customer listed twice in the dataset. 

 

How can I do this using the lookup function or is there any other method using which I can achieve this.

 

Note: I don't want to eliminate the duplicate entries I just want to identify them as I require it further in my analysis.

 

Thanks

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi rohitMe,

     

    You can create a calculate column with below formula to lookupvalue from table2.

    Call ID from Table2 =
CONCATENATEX (
    FILTER (
        ALL ( Table2 ),
        Table2[CUSTOMER MOBILE] = EARLIER ( Table1[CUSTOMER MOBILE] )
    ),
    Table2[CALL ID],
    ","
)

     

    Regards,

    Xiaoxin Sheng

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi rohitMe,

     

    It seems like you want to use customer mobile and call id to check two tables and return a tag if this records are appears in both two tables, right?

     

    If that is a case, you can add a calculate column to table 1 to get current row contents(CUSTOMER MOBILE,CALL ID), then use these variables to check table 2.

     

    Sample:

    Is Appear Twice =
VAR _cMB = Table1[CUSTOMER MOBILE]
VAR _cID = Table1[CALL ID]
RETURN
    IF (
        COUNTROWS (
            FILTER (
                ALL ( Table2 ),
                Table2[CUSTOMER MOBILE] = _cMB
                    && Table2[CALL ID] = _cID
            )
        )
            > 0,
        "Y",
        "N"
    )

     

    Regards,

    Xiaoxin Sheng

    • rohitMe's avatar
      rohitMe
      Advocate I
      8 years ago

      Anonymous

       

      Actaully instead of the tag being "Y" or "N" I want to return the Call ID from table 2 (if calculated column is created in table 1) of the record that appears in both table.

       

      Also do tell me how to do this by using the customer mobile column only to check whether the record is in both the table.

       

      Regards

      Rohit

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi rohitMe,

         

        Try below formula:

        Is Appear Twice =
VAR _cMB = Table1[CUSTOMER MOBILE]
VAR _cID = Table1[CALL ID]
RETURN
    IF (
        COUNTROWS (
            FILTER (
                ALL ( Table2 ),
                Table2[CUSTOMER MOBILE] = _cMB
                    && Table2[CALL ID] = _cID
            )
        )
            > 0,
        _cID
    )

        It will return 'call id' if current 'call id' also appears in another table, blank result means not have duplicate records.

         

        Regards,

        Xiaoxin Sheng