Combination and nearest value

[tansh0099]

Hi experts!

I have built few workflows earlier, but combination and nearest value match is something that I don't know much about it.

there are 2 different data sets like the below table: 

Data set 1	Data set 2
Legal.Entity.ID	Base.USD.Amount	Legal Entity ID	Base USD Amount
830	4046642	830	-1724333
		830	-8926592
		830	10650925
		830	-6604283
		830	-2.7E+07
		830	4370243
		830	214050

i want to find out the combination of Base.USD.Amount in data set 1 from Base USD Amount from data set 2 (i have bold the numbers in data set 2 which sums up to the amount in data set 1) and get the combinations from data set 2 as output.

secondly, after doing the above condition i also want to check  (for the rest of the data from data set 2) the nearest value. example shown in below table.

Data set 1	Data set 2
Legal.Entity.ID	Base.USD.Amount	Legal Entity ID	Base USD Amount
830	4046642	830	-1724333
		830	-8926592
		830	4046641.5
		830	-6604283
		830	-26706162
		830	4370243
		830	214050

i have bold the nearest value in data set 2 and that would be my 2nd output 

Please assist if anybody knows how to do it.

Thanks,

Tanushree 

[OllieClarke]

Hi @tansh0099 are you looking for any combinations, or just 2 value combinations?

[tansh0099]

Hi Ollie
i want values 

[tansh0099]

Anyone can assist here please?

[Christina_H]

Here's a workflow that finds the closest single value from second data set, and the closest combination of two values.  It's not entirely clear if that's what you want, but hopefully it will get you started.

[tansh0099]

Hi @Christina_H  thanks for sharing your expertise 
its working fine for the table i provided but my original data hai 40 rows in data set 1 and 18-20k rows in data set 2
its not working when i am trying the input the original data sets

[Christina_H]

@tansh0099 Try this version.  It wasn't completely set up to work with data for multiple entities, I hadn't included the entity IDs in the sort and sample tools.

[peterr_h]

Hello!

I'm not certain that I completely understood the request, but if I did, then please see the attached.

The method that I used involved appending the second dataset to itself, removing records where I had joined to themselves (i.e. remove where row 1 appends to row 1, etc.), and then removed duplicate instances of these (i.e., keep where row 1 appends to row 2 but remove where row 2 appends to row 1). This implementation was all made possible using RecordID and creating what's eventually a UID for each combination.

I then simply calculated the sums and differences (used too many tools as I was rushing but this could be fine-tuned), and then ordered the differences to find the closest absolute difference to the desired value.

If I've understood correctly then I hope this helps, if not, thanks for a fun little project anyway!

[tansh0099]

Hi All,

every time i am using append tool, its giving me error : Error: Append Fields (2): There were more than 16 records in the source.

Not sure what to do here

[ShankerV]

Hi @tansh0099 

Please modify this to Allow all appends