Hi All,
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.
The result should look like
Help and a sample workflow appreciated.
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)
Hi mlauber71,
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.
OK, I produced a solution with the help of Hive/SQL 
You could adapt the ORDER statement to get a different sorting.
This should give you the result you want. I would have to see to do this with just KNIME nodes.
Hello @sauravh86,
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.
Br,
Ivan
Hi Ipazin,
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)
In the file for check for track 189, I am not able to calculate the position
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)
Current Prev Next Postion
254 ? 2895 1
2895 254 2824 2
2923 2186 2979
2186 2202 2923
2202 2231 2186
2231 2288 2202
2288 2289 2231
2289 2398 2288
2398 2435 2289
2435 2469 2398
2469 2565 2435
2565 2601 2469
2601 2602 2565
2602 2627 2601
2627 2702 2602
2702 2718 2627
2718 2758 2702
2758 2791 2718
2791 2803 2758
2803 2824 2791
2824 2895 2803 3
2979 2923 3023
3023 2979 3045
3045 3023 ?
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)
I will have a look but it might take some time. And you might have to give some more explanations. What data is there in the beginning?
Sure, I need to deliver it by Monday sooner the better 
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)
Position_test.xlsx (15.0 KB) Attached is the source file, since i cant upload the csv i have converted the file to xlsx. Help really appreciated
Hi @sauravh86,
this workflow should give you the outcome which you expected.
BR
Hermann
Example.knwf (135.2 KB)
