Merge: Assigning data into bands?

DarrenElding · April 7, 2015 12:24PM

Hi,

I have a shipment file with a list of shipments of different weights. I have a second data table with a list of weights alongside the weight band descriptions, e.g. 0kg = 0-5kg, 5kg = 5-10kg, 10kg = 10-30kg, etc. I want to merge the weight band description field into the shipment file so that, for example, a 25kg shipment is assigned to the 10-30kg weight band. The problem is that in the merge function if I select the shipment weight to be 'greater or equal to' the weight in the weight band file I get a many to many error as a 25kg shipment is greater than 10 so falls into the 10-30kg band but is also greater than 5kg so also falls into the 5-10kg band.

Is there a way I can get it to assign only the 'highest' weight band in which the shipment falls and not every weight band below that as well?

Many thanks

Darren

paola · April 7, 2015 12:30PM

You will need fields [Min] and [Max] in order to do 2-field merge with the target value, where the merge will satisfy both criteria: greater than [Min] AND smaller than [Max].

See the image and similar demo here:
http://forums.visokio.com/discussion/comment/7974/#Comment_7974

daniel · April 7, 2015 4:11PM

Another way is to use a new formula field that uses IF clauses to assign the weight category and merge to this new field.

IF ( Weight<5 , "0 - 5kg", Weight<10 , "5 - 10kg",Weight<30 ,"10 - 30kg", "30kg+" )

DarrenElding · April 8, 2015 5:32AM

Thanks Paola and Daniel!
I had been assigning the weight bands manually in an If statement but I now have the situation where I have 15,000 weight categories so this is no longer possible.
The min and max method with a 'Join' condition on both does seem to work but it does take a very long time to run due to the numbers of records I have (57,000 shipments and 15,000 weight bands).

Perhaps there is a better way of doing this in Visokio. I am trying to replace the manual Excel (vlookup) way of assigning rates to shipments with a more automated Visokio method. The problem I am having is that while Excel does require a lot of manual set up the vlookup does calculate the rates very quickly while the 'merge' function in Visokio takes far longer. Is there a better approach in Visokio?

Thanks again for any suggestions!

paola · April 8, 2015 5:43AM

Please post a few lines of the lookup file (small csv file) you're using at the moment and we can test the method here.

DarrenElding · April 8, 2015 7:42AM

Hi Paola,

Here are the two files I am trying to merge. The joins are as follows:
Origin country = Origin country
EXP Zone 1 = To Zone
Weight <= Weight From<br />Weight >= Weight up to and including

This method does seem to work but the merge takes a very long time.

Many thanks

Darren

paola · April 8, 2015 2:55PM

Hi Darren,
in this Merge block match is made after 4 criteria are satisfied simultaneously. It is the best /most efficient way of accomplishing the task.
It ran quickly on my machine, but the sample dataset is small. You could try to split the merge and do it in 2 steps (blocks) with only 2-3 criteria, or to filter in one country at a time... See attached demo.
Please let me know which version of Omniscope you're using and how long it took to complete the operation, also the size of your dataset.

CRead · April 14, 2015 10:56AM

I resolved a similar challenge when I found that the < and > joins were slow.

Filter the fields on the left hand side to the minimum necessary
Then de-duplicate it
Join on equals joins only (many:many) which creates A LOT of records
Pass the output into a field organiser block with a formula "filter" AND( weight < max , weight > min )
Next a filter block for filter = true
Merge back into the full left hand side stream with the information needed

The formula evaluates very fast, even over a few tens of millions of records, and the operation runs in seconds rather than minutes.

	Rate card.csv	6K
	Shipments.csv	221K

	WeightBands.iok	62K
	Mergex4.JPG	141K

Welcome!

Categories

Ideas Parade

Tagged