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

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


      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