Hi @sauravh86,
I suggest you to take small data as an example with header and then explain how it should be may be with a picture, arrows pointing and references, so that others can understand easily and help you better.
Regards,
Pavan
1 Like
Maybe you can again give us a consistent example and discuss which columns are what and what information you have at the start of your test and what are the rules. And it would be ideal if the sample data carry the names of the columns you use in your description.
As a remark: the loop in the top of your workflow example is not closed so maybe this would not give you the information you want.
I used the Position_test.xlsx and though about the various rules and remarks and came up with this solution that from my perspective would represent the result you gave us for 179 and 189.
You could check it out in the /data/ subfolder.
data/data_result.xlsx
kn_forum_26479_hive_group_rank2.knwf (323.9 KB)
At the heart of this thing is a ROW_NUMBER() and PARTITION function (I used Hive because I think the free/standalone DBs would not provide us with something that would fit into a single example).
Thanks guys for the reply, I am attaching the actual files and expected result file.Actual_test.xlsx (14.7 KB) Expected_Position.xlsx (14.5 KB)
The track id column has the different conveyor belt numbers and the previous, next and id(current position) are the position of the trolleys on the given conveyor belt. I need the positions of the trolleys on that conveyor belt. so, for trolleys the one in the next column is the trolley at position 1 and so on.
The next becomes current for the next position.Consider them as double linked list grouped by conveyor belts
Looking into your Expected_Position table the output of the sorter node ‘order by Groupid & Position’ looks very similar. The only thing is from which datarecord for Position 1 you get the entry for previous?
Because this is logical inconsistent.
BR
For the first position we the prev id should be null. i had removed the records and i have added them back. Attached are the new filesExpected_Position.xlsx (15.5 KB) Actual_test.xlsx (15.4 KB)
Ok. What is now the differece between your output and the upper sorter node from my example?
Positions for trackid 189 is wrong. The position 20 in the screenshot should be position 2. The nextid is 2895 for position 1 and 2895 id gets position 20 as per your logic but it should be 2.
Ok. I’m not sure which column i had used for rank. Probably i didn’t used the column row which i generated in the previous java snippet.
in your table creator i can only see only column for ids which is kinda wrong. we need to do a vlookup for next id in id column.
No you don’t. The Java Snippet returns the record number and the rank node should give you based on the record number and the trackid as group condition the correct position number.
I would really appreciate if you can upload the workflow with correct result.
The thing is Ids is not ascending order so we cant assign ranks to them
That is the reason why i have implemented the record number, assuming the records itself are in the correct order.
OK I hope I finally got this. With two loop nested into each other and (again) not using the recursive loop but storing the data in between into a temporary file.
The workflow assumes the start of an inner loop is an empty cell (one empty cell) or a “?”. Otherwise it may throw an error. I tested it against your target list and the positions do all match.
3 Likes
@sauravh86 glad it worked. One more hint. You might want to check out the usage of recursive loops for your task like in @Luca_Italy’s example above.
Only because I struggle to use and trust the inner workings of these special loops does not mean they might not be useful in creating a more knimey experience.
For me this storing and re-reading of tables feels more secure but it also might slow down the process if you need this in a production environment.
1 Like
Hi @sauravh86,
the only thing you have to change in my workflow ist the settings in the rank node. I had indeed previously used the Id for Ranking. This should be changed to ‘ROW’.
2 Likes
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.