Skip to main content
Question

Group by using using multiple attributes


aron
Supporter
Forum|alt.badge.img+16
  • Supporter

I want to use LineBuider to connect pairs of points. Thing is that they need to be grouped such that Attribute_A equals Attribute_B. All features contain both Attribute_A and Attribute_B. I am sure that there is a clever workaround for this, but I cant figure out how.

11 replies

davtorgh
Contributor
Forum|alt.badge.img+11
  • Contributor
  • August 25, 2020

Hi @aron​ ,

 

you can try out the following techique:

  • get all the features where attribute A = attribute B with a Tester;
  • group the feature by attribute A with an Aggregator and build a list with all the attributes;
  • create an attribute _group_id with a Counter;
  • deaggragate the features with a Deaggregator and explode the previous list;
  • go to LineBuilder and group by attribute _group_id.

See the attached screenshot.

Hope that helps!

 

screenshot


davtorgh
Contributor
Forum|alt.badge.img+11
  • Contributor
  • August 26, 2020
davtorgh wrote:

Hi @aron​ ,

 

you can try out the following techique:

  • get all the features where attribute A = attribute B with a Tester;
  • group the feature by attribute A with an Aggregator and build a list with all the attributes;
  • create an attribute _group_id with a Counter;
  • deaggragate the features with a Deaggregator and explode the previous list;
  • go to LineBuilder and group by attribute _group_id.

See the attached screenshot.

Hope that helps!

 

screenshot

Hi @aron​ ,

 

maybe there is an even simpler solution. Go straight from the Tester to the LineBuilder and group by attribute A.


aron
Supporter
Forum|alt.badge.img+16
  • Author
  • Supporter
  • August 26, 2020

Unfortunately I can't get this to work. I realise I was a bit vague in my initial question.

 

I have two different "data streams" with points. Lets call them Point1 and Point2. Each point has Attribute_A and Attribute_B.

 

I would like to find features from Point1 where Attribute_A or Attribute_B match Attribute_A or Attribute_B from Point2. And then connect the matching pairs with a line.


jkr_wrk
Influencer
Forum|alt.badge.img+29
  • August 26, 2020

On the stream with point B change the attributename to Attribute_A and use this as GroupBy parameter. If you want the attribute back you can copy the Attribute_A parameter back to Attribute_B afterwards.


ebygomm
Influencer
Forum|alt.badge.img+39
  • Influencer
  • August 26, 2020
aron wrote:

Unfortunately I can't get this to work. I realise I was a bit vague in my initial question. 

 

I  have two different "data streams" with points. Lets call them Point1 and Point2. Each point has Attribute_A and Attribute_B.

 

I would like to find features from Point1 where Attribute_A or Attribute_B match Attribute_A or Attribute_B from Point2. And then connect the matching pairs with a line. 

Will there only ever be one match?

 

I think I'd be inclined to extract the Coordinates as Attributes from the dataset, then use an InlineQuerier to transfer  coordinates from the Point2 dataset to the Point1 dataset where Attribute A = Attribute A or Attribute B = Attribute A or AttributeB = AttributeB or AttributeA = AttributeB, then vertex creators to create the line 

SELECT P1.AttributeA, P1.AttributeB, P1._x, P1._y, P2._x, P2._y FROM Point1 P1, Point2 P2
WHERE P1.AttributeA = P2.AttributeA
OR P1.AttributeA = P2.AttributeB
OR P1.AttributeB = P2.AttributeB
OR P1.AttributeB = P2.AttributeA

 


davtorgh
Contributor
Forum|alt.badge.img+11
  • Contributor
  • August 26, 2020
aron wrote:

Unfortunately I can't get this to work. I realise I was a bit vague in my initial question. 

 

I  have two different "data streams" with points. Lets call them Point1 and Point2. Each point has Attribute_A and Attribute_B.

 

I would like to find features from Point1 where Attribute_A or Attribute_B match Attribute_A or Attribute_B from Point2. And then connect the matching pairs with a line. 

@aron​ ,

 

do you mean this?

point1.A = point2.A or
point1.A = point2.B or
point1.B = point2.A or
point1.B = point2.B

 


davtorgh
Contributor
Forum|alt.badge.img+11
  • Contributor
  • August 26, 2020
aron wrote:

Unfortunately I can't get this to work. I realise I was a bit vague in my initial question.

 

I have two different "data streams" with points. Lets call them Point1 and Point2. Each point has Attribute_A and Attribute_B.

 

I would like to find features from Point1 where Attribute_A or Attribute_B match Attribute_A or Attribute_B from Point2. And then connect the matching pairs with a line.

Hi @aron​ ,

 

I think @ebygomm​  found a nice solution . I can't try out it right now, but it should work using two VertexCreator in sequence in mode 'Add Point'.

 

Bye.


dustin
Influencer
Forum|alt.badge.img+31
  • Influencer
  • August 26, 2020

This should do the trick.


aron
Supporter
Forum|alt.badge.img+16
  • Author
  • Supporter
  • August 27, 2020
ebygomm wrote:

Will there only ever be one match?

 

I think I'd be inclined to extract the Coordinates as Attributes from the dataset, then use an InlineQuerier to transfer  coordinates from the Point2 dataset to the Point1 dataset where Attribute A = Attribute A or Attribute B = Attribute A or AttributeB = AttributeB or AttributeA = AttributeB, then vertex creators to create the line 

SELECT P1.AttributeA, P1.AttributeB, P1._x, P1._y, P2._x, P2._y FROM Point1 P1, Point2 P2
WHERE P1.AttributeA = P2.AttributeA
OR P1.AttributeA = P2.AttributeB
OR P1.AttributeB = P2.AttributeB
OR P1.AttributeB = P2.AttributeA

 

Thanks @ebygomm​ !

 

I tried this but could not get it to work for me. First time with this transformer for me and next to no previous experience of SQL, so i'm in pretty deep water...

 

I got it to work so far that it does merge the attributes. But it also creates loads of duplicates. Any ideas on how to produce a "clean" output? 

(I tried to clean up the data with a Matcher, but did not manage to get it to perform well)


ebygomm
Influencer
Forum|alt.badge.img+39
  • Influencer
  • August 27, 2020
ebygomm wrote:

Will there only ever be one match?

 

I think I'd be inclined to extract the Coordinates as Attributes from the dataset, then use an InlineQuerier to transfer  coordinates from the Point2 dataset to the Point1 dataset where Attribute A = Attribute A or Attribute B = Attribute A or AttributeB = AttributeB or AttributeA = AttributeB, then vertex creators to create the line 

SELECT P1.AttributeA, P1.AttributeB, P1._x, P1._y, P2._x, P2._y FROM Point1 P1, Point2 P2
WHERE P1.AttributeA = P2.AttributeA
OR P1.AttributeA = P2.AttributeB
OR P1.AttributeB = P2.AttributeB
OR P1.AttributeB = P2.AttributeA

 

If there is only one pair matching between each attribute I don't think there should be any duplicates with this method. If in fact the value of AttributeA in Point1 dataset exists in both AttributeA and AttributeB in the Point2 dataset then you need some logic to decide which one to match. 


aron
Supporter
Forum|alt.badge.img+16
  • Author
  • Supporter
  • August 28, 2020

Thanks for all the help everyone! I learnt much. In the end I found a solution where the problem got solved earlier in the workspace, and in a much simpler way.


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings