I need to implement a multirow formula similar to Alteryx.
Problem:
I have my data like
I need to group by the groupIds and then calculate the positions based on next and PrevId. If Previous id is null position is 1 and then increment it based on Id column.
Could you upload an example and maybe explain what it is with the Previous and Next Information. Are they relevant for the Position or should it be possible to just use the IDs and their number (or position).
Most likely you could so a Group Loop and use the Rowindex or a LAG column node (maybe combined with a Math node)
Thank for the reply.
So id is the current state, previous is the last state and next is the next state. I need to calculate the position depending on the last , current and next state grouped by GroupIDs.
For example for Groupid 179 in row 0 previous id is null that means position is 1 , for row 2 previousid is current id for row 0 , hence the position is 2. We have to calculate positions for a groupid untill the next id is null. I hope i have not confused you more.
if I understood it correctly to get your position column you can use Rank node as you can define Group column there. Only prior to it you can add Counter Generation node based on which you will do ranking.
The problem is not yet solved the IDs, previousIds and Next ids are not sorted in the data. Attached is the copy of the data. I would really appreciated if you can send me the logic for sorting. We want a kinda vlookup in the table itself.Book2.xlsx (13.5 KB)
I am not sure if I understand what you want. Have you checked out the example with the Hive command and Rank.
You might want to describe in more detail what your original data is (maybe give an example of that) and describe or define what you would like to happen to the data and maybe a matching result to your data.
HIve command will not work because you are sorting based on current, prev and next which will not work in this case.
In the below mentioned example the first next 2894 becomes current in the fourth last row. So basically sorting will not work. We need a recursive loop and self join. (vlookup in excel)
I am very sorry I do not understand you varying examples in different formats, eg is the order a given and might be represented by a sequence of numbers 1,2,3, or does it depend on the Next.
My suggestion would be to try to explain again in more detail what you want to achieve - maybe what kind of loop or iteration you would like because such things should be possible in KNIME.
Maybe you create an example and explain what you want to have.
@mlauber71 attached are two excel sheets . One is the actual data and second is the expected output. We just have to grab a nextid and search it in the currentid /ID column and calculate the positionExpected.xlsx (13.0 KB) Actual.xlsx (9.3 KB)
From a quick look it seems to be the same data like before and I still might not understand what the initial information is and what should be the outcome.
Also an example should cover the entire challenge.
in the actual excel sheet, 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 the trolley whose previous position is empty becomes the first trolley and the one in the next column is the trolley at position 2 and so on. Consider them as double linked list grouped by conveyor belts. Hope it simplifies the prob
I have made a sample workflow, its working for a single track but not working with group of tracks.Attached is my workflowPosition_test.knwf (131.9 KB)