Pl. see attached zipped excel file to find the explanation of the problem statement
Solved
How to merge two datasets based on matching attributes
Best answer by takashi
Hi @bey_atkins2009, if I understand the requirement correctly, this workflow might help you.
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+62- Celebrity
A combination of FeatureMerger (or DatabaseJoiner) and ListExploder would be a good place to start.
+21- Safer
Hi @bey_atkins2009, Combining Multiple Streams of Data goes through a couple examples of merging data. Like @redgeographics mentioned, you'll want to look at the FeatureMerger.
+11- Safer
Hi,
@bey_atkins2009
I had a closer look at the example you provided. And I think both @redgeographics and @TiaAtSafe are definitely correct in suggesting FeatureMerger, also ListExploder. A bit more detail on what I'm thinking:
1. Merge the FROM NODE and END NODE from Dataset 2 with the ID in dataset 1 in parallel, and make sure to process duplicate suppliers, and Generate a list from them. (because, each From and End node will be matched with more than one ID in Dataset 1)
2. for each merged result, use the ListExploder to break down the list into individual features, and use a NullAttributeMapper to map missing attribute (from the UnmergedRequestor and UnusedSupplier port) to 0, and 0:00, depending on what attribute it is.
3. Note, when you explode the list, consider adding a prefix to each stream, to differentiate the From and To matches. this will help when you merge in the next step.
4. Use another FeatureMerger to merge the two streams together, basedon pipeID, and the time attributes from both streams.
5. After the last FeatureMerger, you might want to look at mapping missing attributes, like we did in step 2, as well.
6. in the end, you can clean up the unwanted attributes, using AttributeManager, AttributetKeeper, or AttributeRemover.
Here is a screenshot of the workflow. Not sure if it the most efficient approach. Hope it gives you some ideas, anyway.
Hi,
@bey_atkins2009
I had a closer look at the example you provided. And I think both @redgeographics and @TiaAtSafe are definitely correct in suggesting FeatureMerger, also ListExploder. A bit more detail on what I'm thinking:
1. Merge the FROM NODE and END NODE from Dataset 2 with the ID in dataset 1 in parallel, and make sure to process duplicate suppliers, and Generate a list from them. (because, each From and End node will be matched with more than one ID in Dataset 1)
2. for each merged result, use the ListExploder to break down the list into individual features, and use a NullAttributeMapper to map missing attribute (from the UnmergedRequestor and UnusedSupplier port) to 0, and 0:00, depending on what attribute it is.
3. Note, when you explode the list, consider adding a prefix to each stream, to differentiate the From and To matches. this will help when you merge in the next step.
4. Use another FeatureMerger to merge the two streams together, basedon pipeID, and the time attributes from both streams.
5. After the last FeatureMerger, you might want to look at mapping missing attributes, like we did in step 2, as well.
6. in the end, you can clean up the unwanted attributes, using AttributeManager, AttributetKeeper, or AttributeRemover.
Here is a screenshot of the workflow. Not sure if it the most efficient approach. Hope it gives you some ideas, anyway.
For the 1450 unmatched requestors, I thought that NullAttributeMapper will expand the list and put in zeroes for pressure and elevation.
Could you kindly clarify.
Thanks
Yogish
- Best Answer
Hi @bey_atkins2009, if I understand the requirement correctly, this workflow might help you.
Hi @bey_atkins2009, if I understand the requirement correctly, this workflow might help you.
Update. If it's guaranteed that there isn't any exception in the format for TIME values (%H:%M hrs) at all, it's not essential to calculate the minutes on the Dataset 1. The workflow could also be a little simpler,
Thanks for your solutions. This last solution you provided works for me. Many thanks again.
As a further step, I would like to segregate the data of 'From_Pres_Elev' and 'To_Pres_Elev' into several ranges like <5, 5-10, 10-20, 20-30, 30-40, 40-50 and finally >50 and obtain a sum and a count for each of the ranges. I am thinking of using "TestFilter" and "StatisticsCalculator" transformers and hoping they would do the job.
Do you have any suggestions or corrections on this?
Looking forward to your response.
Regards
Yogish
+62- Celebrity
Hi @takashi,
Thanks for your solutions. This last solution you provided works for me. Many thanks again.
As a further step, I would like to segregate the data of 'From_Pres_Elev' and 'To_Pres_Elev' into several ranges like <5, 5-10, 10-20, 20-30, 30-40, 40-50 and finally >50 and obtain a sum and a count for each of the ranges. I am thinking of using "TestFilter" and "StatisticsCalculator" transformers and hoping they would do the job.
Do you have any suggestions or corrections on this?
Looking forward to your response.
Regards
Yogish