I am trying to join two datasets (csv format) by using conditional if statements. First dataset is accident information which does have a unique id (caseno) and the second dataset is roadway information which does not have a unique id. To join the accident file to the road file, I have to match multiple columns, if county in dataset 1 equals county in dataset 2 then we match county route (cnty_rte) in dataset 1 to dataset 2. If they are equal, then we match milespost in dataset 1 to a range in dataset 2, beginning of milepost (begmp) to end of milempst (endmp). So, if the milepost falls in this range, the accident would be a perfect match to the road location. My end goal is to have an additional column in dataset 1 which would denote the unique id (caseno) or if there is no match then '0'.
The link for the file is; (www.dropbox.com/s/ytmm3yw10891r4n/stack_Overflow.xlsx?dl=0)
This is the statement which is not working for me
=IF([@county]=nc11acc[@county],IF([@cntyrte]=nc11acc[@[cnty_rte]],IF(nc11acc[@milepost]>=[@begmp],IF(nc11acc[@milepost]<[@endmp],[@OID],0))))
It just gives me values which are False, some 0 values and then some #Values.
The link for the file is; (www.dropbox.com/s/ytmm3yw10891r4n/stack_Overflow.xlsx?dl=0)
This is the statement which is not working for me
=IF([@county]=nc11acc[@county],IF([@cntyrte]=nc11acc[@[cnty_rte]],IF(nc11acc[@milepost]>=[@begmp],IF(nc11acc[@milepost]<[@endmp],[@OID],0))))
It just gives me values which are False, some 0 values and then some #Values.
