Transfer and sort values from different rows with geo-coordinates one below the other into a new column.

Hi,

i want to sort cells with geo-coordinates into an new column.

from

to

I want to use the String Manipulation Node but i don´t know a way how to change the format of the coordinates.

Like this way:
replace($Coordinate#1$,"?",substr($Coordinate#3$,0 ) )

*Before that, I would have to convert the missing value as well

Thx for a tipp!

Greetings

Could you provide sample data (or fake data but of the same format)? This problem appears very interesting, but a solution would require knowledge of what to look out for.

As well, why would you like to use string manipulation? In my mind, I’m thinking of a groupby/pivot strategy to deal with sorting the data.

1 Like

Hi @USCHUKN1ME , there are a few things to point out here:

  1. Your output shows Coordinate#4 which I don’t know where it came from as it does not show in your initial table.
  2. Your Coordinate#* tables are of type Coordinate, and what you see is one of the rendered format, so their true value might not necessarily be what you see, and manipulating them can result in unexpected results because of that.
  3. Regarding this replace($Coordinate#1$,"?",substr($Coordinate#3$,0 ) ), are you attempting to replace the missing values of column Coordinate#1 by values of column Coordinate#3? If that is the case, this will not work. There is no string “?” there. If it was a string “?”, you would see a black “?”, as opposed to a red “?”. A red “?” is simply a way for Knime to display a missing value/cell. There is no “?” whatsoever there. It’s just a display thing. This is better done via the Rule Engine instead, where you can evaluate if a column has missing value via the MISSING operator.
    Basically if I translate what you are attempting to do into a Rule Engine, it would look something like this:
MISSING $Coordinate#1$ => $Coordinate#3$
TRUE => $Coordinate#1$
  1. Also, substr($Coordinate#3$,0 ) is simply $Coordinate#3$

  2. How should the sort or group be? By ZIP+Ort#(Nr) only or by Datum and ZIP+Ort#(Nr)?

Your attempt to copy the Coordinate#* columns to column Coordinate#1 will not work as a solution, since you need to have as many entries of ZIP+Ort#(Nr) (and possibly with Datum, depending on your answer for question #5 above) as you have Coordinates for each of them. What I mean is, the 4 lines you have in your output are for 2 lines from your input. So you need to add additional copies of ZIP+Ort#(Nr) (and possibly with Datum) in new lines to make up for the 4 coordinates.

So, the whole approach would be best resolved by what @victor_palacios is suggesting, which is a combination of groupby/pivot/unpivot. This will allow you to “move” all of the Coordinate#* columns into 1 column, where you can then sort them. Also, most probably there will be no need to convert/manipulate the coordinates, and they can be left as is.

1 Like

Hi,

Have you in mind something like this

Transfer values.knwf (29.4 KB)

When you have all data in one column you can do what you want with it

Regards

@bruno29a @andrejz @victor_palacios

at first thx for your help!

@victor_palacios
It’s roughly the creation of a itinerary.
The places will be communicated for each day of sales staff.

Source is an Excel file with the following format:

After some other processing steps in Knime, I have the following intermediate result.

Next step in the workflow : Determination of distances Medium Geo Distance nodes.

To display all the places per day (date) in the MapViewer, I need the coordinates coordinate # 1, … # 2, … # 3, … # 4 all in one column, because the MapViewer Node allows only one.

MapViewer Node

A separate step would be the evaluation of the hazardous kilometers.

I hope I could explain it reasonably understandable.

@bruno29a

Unfortunately, this operation does not work in the string manipulation node.
* **MISSING $Coordinate#1$ => $Coordinate#3$** **TRUE => $Coordinate#1$** *
I do not find a solution how to replace the Missing Value specifically with Coordinates.

@andrejz
Exactly, something should be done.


2022-03-21 16_47_11-KNIME Analytics Platform

ERROR Loop End (deprecated) 3:814 Execute failed: Input table’s structure differs from reference (first iteration) table: Column 2 [Coodinaten sorted2 (GeoCoordinateCell)] vs. [Coodinaten sorted1 (GeoCoordinateCell)]”

Something is wrong in my configuration.

Greetings

Can you attach a sample of the data in excel format? Or fake data of the same format so we can try making workflows which solve this problem.

Also, on the loop end did you enable changing table specifications? That may also help.

Have you tried groupby by the way? Groupby by date and address and ask for first value for coordinate 1, etc. is the approach I am currently thinking of.

Please attach data and a workflow so we can better assist you. Thanks~

Hi @USCHUKN1ME ,

As I mentioned, this is done via the Rule Engine. It’s a Rule Engine syntax, not a String Manipulation syntax.

I also asked this question:
How should the sort or group be? By ZIP+Ort#(Nr) only or by Datum and ZIP+Ort#(Nr)?

Hi @USCHUKN1ME , from what I see, you have 2 requests here.

  1. Move Coordinate #3 to Coordinate #1, and Coordinate #4 to Coordiate #2, so that you can calculate your distance between Coordinate #1 and Coordinate #2.
    You can do this via 2 Rule Engines using the rules I gave you:
MISSING $Coordinate#1$ => $Coordinate#3$
TRUE => $Coordinate#1$

and

MISSING $Coordinate#2$ => $Coordinate#4$
TRUE => $Coordinate#2$

This is of course assuming that when you have Coordinate#3 and Coordinate#4, Coordinate#1 and Coordinate#2 are empty, as per your screenshot.
You can then run your Distance calculation between Coordinates 1 and 2.

  1. Moving all coordinates into 1 column. Assuming you did the step above, you should end up with all the coordinates in Coordinate#1 and Coordinate#2, you can now get rid of Coordinate#3 and Coordinate4, create a collection made of Coordinate#1 and Coordinate#2, then ungroup.

Something like this will take care of both:

Sample data to simulate what you have from your screenshot:

After moving Coordinate#3 to empty Coordinate#1, and Coordinate#4 to empty Coordinate#2:
image
You can now run your Distance calculation between Coordinate#1 and Coordinate#2 which will cover all coordinates.

And moving all coordinates into 1 column:
image
You can now plot your Map Viewer

Here’s the workflow: Move multiple columns into 1 column.knwf (27.4 KB)

Hi,

To calculate the distance between two places you can use the Haversine formula

Haversine.knwf (11.6 KB)

Regards

Hi,

Can this workflow
Calculate distance.knwf (49.7 KB)
help you in some way?

Regards

Hi @victor_palacios ,
.
enclosed a version shortened of the source file
TR-ReiseKosten 1-22.xlsx (17.1 KB)

Greetings


coordinates.knwf (9.0 KB)

The data provided wasn’t the same as the picture you first posted, so here is my approach using table creator based on the data first shown.

Hello @victor_palacios ,

sorry i misunderstand. i guess you want the source file.

Now i attached the file you mean:
Testexcel-Sortierung .xlsx (9.5 KB)

I test you Wokflow and got following results:



After unpivoting, there should be new columns.

1 Like

@victor_palacios If you calculate the geodistance based on only one column which some nodes in KNIME e.g. GeoDistance Node offer then what distances are actually calculated? Is it like a cross product that each row to each rows distance is calculated? The Description does not tell that. Do you know that by any chance?
If the Distance is calculated based on 2 columns in the same row it’s more clear to understand for me
Thanks

Sorry, I don’t know about that since it is not a node I can access directly from KNIME. I can only comment on nodes users can get directly from KNIME. But perhaps if you open that in a new thread, the person who made that node can answer?

Hi,

Here is a workflow on which you can work on

Calculate distance.knwf (65.2 KB)

1 Like

Hi @victor_palacios,

that’s what I wanted to achieve :slight_smile:

:+1:

@bruno29a cool, thx for it

Glad I could help. Could you mark the “solution” to your post so others can easily find it in the future. Thank you~