Visokio website     Downloads     Video tutorials     KnowledgeBase  
Merge: Assigning data into bands? - Visokio Forums
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
  • 7 Comments
  •     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.

    image
    Attachments
    WeightBands.iok 62K
    Mergex4.JPG 141K
  •        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.

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership

Tagged