I need to only increment by an index of 1 starting at 1 if a component part of a parent is in a specific department. In other words, the order will matter, and the incrementing will start over based on the parent part and department.
Not sure what tables/fields you’re actually using… nor your intended relationships… but I would think a calc field using the below structure would get you close:
ROW_NUMBER() OVER(PARTITION BY ParentPN, Dept ORDER BY something)
2 Likes
Basically, it’s an indented BOM recursion and the levels that are in a specific department I need to re number starting at 1, 2, 3 etc. I have tried Row number but it’s as if it ignores the order of the bom structure and sometimes starts at 1 and other times starts at a higher digit. If the specific department come first in the sequence, then it’s a 1 else it could be a higher digit depending on the sequence.
Then you did something wrong. Show us what you tried and we’ll help you fix it.
3 Likes
MORE THEFT!? 
1 Like
Ahem, I invented it with the one for @Mark_Wonsil . 
Looks an awful lot like my “JKane Signal” 
We at least both started from stealing the same base image, haha.
1 Like
I figured it out. I didn’t include the department in the partition by section. Thanks for the help.
5 Likes
The next issue. This increments off the dept being different from the row above. What if they are the same department but have a different parent part? How do you start increment over? In the example below, the Count Column should be 1 then 2 because they are in desired depts but have the same parent.
What column is your department? What is your current expression?
You can put multiple columns in the Partition By statement.
Dept is Character01.
Is it NOT doing this? Can’t tell if this is a hypothetical or if you’re actually seeing it not start the increment over.
So because I have specified in the case function that dept 104 and 107 are to increment by 1 based on the over by clause (partition by parent part, dept) the row number is 1 and 1, respectively. I need this to be 1 and 2, respectively. There are also instances in the data set that may have consecutive dept numbers (104 and 104, respectively) but with different Parents. I need these instances to be 1 and 1, respectively. I believe part of the problem is I can’t seem to order by in an indented bom sort (levels: 0123, 012, 012123, etc)
My thought is that you actually DON’T want to partition by Department then, right? Only by Parent Part.
You’re ruling out specific departments in your case statement (good, fine)… but all other departments should be included together, you don’t want to partition by them.
So your count should reset every time your query hits a new Parent Part… otherwise, it should sequentially count each row (because you really not care about the department value outside of your case statement).
I believe it’s doing the row_numbers for everything, then replacing the value with 0s. So you’re going to to have something like add a calculated field for “include” which would be false for “137,197,195” and true for everything else, then you can put that field in the partition.
2 Likes
Yeah, row number isn’t going to work in this case. You can try a Dense_Rank() expression and move your case statement inside the Order By clause.
Try (hopefully I typed everything okay):
DENSE_RANK() OVER (PARTITION BY Reversal.Calculated_MainPartNum ORDER BY CASE WHEN Reversal.PartWhse1_Character01 IN (137, 197, 195) THEN 0 ELSE 1 END, Reversal.Calculated_Sort)
This MIGHT work… or it might “rank” all of your excluded departments (which get assigned an initial value of 0)… and then “rank” all of your included departments (which get an initial value of 1).
If that happens, you may have to do a CTE to give them a temp_rank of 0 and 1 (based on your case statement), and then do the dense_rank against the rows with a temp_rank of 1… if that makes sense.
I’m sure @Banderson will volunteer to help with the CTE
1 Like
Oh yeah… that could get around the CTE portion in my approach. Make a boolean column to include or exclude from the ranking. As long as that’s in the partition… seems like that would work.
Sorry, I was googling Rank() vs Dense_Rank() when you posted that, haha.
This is what I get doing it that way:
Its ignoring the 0 and also giving crazy numbers (50,51,52,etc)