2 Replies Latest reply on Dec 12, 2016 9:24 AM by qicheng.chen

    Returning Binary value (Yes or no, etc.) on matching 2

    qicheng.chen

      Have this project I'm working on, basically trying to find if a business is home based - by matching corporate address (from dataset#1 vs. home address from dataset#2... We are linking the 2 through an account # which is shared by both data sets)

       

      HOWEVER the problem here lies that in dataset#2 containing the home address has MULTIPLE values for SOME account #'s...Hence when I can't just do a simple IF THEN statement...I tried doing IF ATTR(Corp Address.Dataset#1) = ATTR(Home Address.Dataset#2) THEN Yes else NO, this works if I'm just doing a sample test by account #.... but when I actually try to part the data it doesn't work.

       

      Test Sample:

       

      Account #Corp AddressHome AddressHome based?
      Account #1Corp Address AHome Address AHOME BASED
      Account #2Corp Address BHome Address CNOT HOME BASED (because B =/= C)

       

      So this above works as a separate worksheet,

       

      But like I said before some of them have Asterisks for Home based because there are multiple values with that matching account # in dataset#2... such as this...

       

      Acc #Corp AddressHome AddressHome Based?
      Account #3Corp Address Y*No

       

      because the data is Account # 3, Corp Address Y, then you have Home Address X, Home Address Y, Home Address Z in 3 different rows with that matching Account #, is there a way maybe I can just take only one value?

       

      ^^I'm fine with it saying no as home based in this scenario...

       

       

      So in the summary worksheet I cannot part data that I need (which includes sales/# of employees, etc.) I want to optimally see

       

      Home Based?Not Home based
      Sales 100kSales 300k
      avg # of emp: 2avg # of emp: 25