Hi,
okay, so some more words as explanation. I do get a list with data for each day. Data is the date, a mileage number and an ID number. THe ID number is the unique number of on bike. Mileage is the mileage counter of the bike on the day of repair.
Now there are cases where one bike come back several times. IN this case I do have the same ID on different days. Like below.
Date Mileage ID
7/2/2020 177 1237
7/20/2020 210 1237
7/11/2020 238 1237
No I need to know how often one ID is in the list, if its more then 1 time I need the rank the returns sorted by the Mileage. Normally Mileage and date should return the same sorting results. I also want to know how much mileage is the difference between the first repair and the second repair (my delta)
My issue in PQ is that the grouping by ID is fine, but the sort order inside the nested tables are mixed up.
Step 2 sorting group by
Therefore in step 2 i need to sort my column containing the table from the group by operation.
Then I add an index column for the repairs time order which gives me the ranking of the repairs from 1st repair to n.th repair.
step 4 or column F
Then I need to do the step 4, calculate the mileage. If the "Sequence of cases per ID" is >=2 then take the Mileage [Sequence of cases per ID] - Mileage [Sequence of cases per ID] - 1)" and so so.
If the Number of repairs is only 1 time, or its the first time of repair for the cases the bike is repaired multiple times, my caculation is not working, therefore 0
In other words,
I will look into the delta explananion, thanks alansidman, but not today anymore as i need to go out now.
Bookmarks