• EN
Solved

Merging 2 CSV files based on a number range.

  • 30 October 2018
  • 1 reply
  • 11 views
P

Hi,

I need to merge 2 CSV files based on a number range. In the first file, I have a ZIP Code with the ZIP+4 number (2 separate attributes). In the second file I have a ZIP Code with a ZIP+4 range (Beginning +4 and Ending +4 -- 3 separate attributes). I need to merge the files based on the ZIP+4 number and create a new CSV file with all the info from the first file with an attribute from the second file appended.

For instance, the first file may have ZIP Code 12345 with a ZIP+4 of 1000. The second file may have ZIP Code 12345 with a range of 0500-1500 and these should match.

icon

Best answer by notmyname 30 October 2018, 18:31

View original

1 reply

notmyname
Rank
Badge

Hello,

If I read correctly it sounds like you are trying to join one dataset to another based on the value A being in the Range of Some Value B. There are a few different solutions to this problem and I have found this post:

https://knowledge.safe.com/questions/4744/how-to-merge-features-with-the-feature-merger-usin.html

useful when working on similar problems. The best approach will depend on the amount of data you have. The quickest method to setup is to cross join all of A to B then test filter out the items that do not match the range. This does not work for large datasets as the output is the number of rows in A multiplied by the number of rows in B, before it is filtered.

The other approach I use is the inline querier, which takes a bit to get the hang of, but is simple once you understand it. It however, slow for large datasets.

 

hope this helps. I think details of each are in the post I linked

Reply


Community Stats

28,777
Posts
109,726
Replies
36,604
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.