BAQ - Calculating Row Number for closest PO Date to Case Creation Date without duplicates

Epicor ERP 10
1

I have a BAQ that shows warranty cases. I had a question a week ago on this, and I ended up just deciding to take the closest PO date for a part to the Case Creation date. I am trying to get this to work in a calculated field called RwNm, and essentially I want every unique part to have a RwNm of 1. If there are duplicate parts…it should find the closest PO Date to the Case Creation Date, and make that RwNm 1, and every duplicate afterwards should be 2, 3 etc. I filtered down to only get RwNm 1, but I can’t seem to make this work properly…

(ROW_NUMBER() OVER(
    PARTITION BY HDCase.HDCaseNum, PODetail.PartNum 
    ORDER BY Abs((Convert(int, Convert(varchar, HDCase.CreatedDate, 112)) - Convert(int, Convert(varchar, POHeader.OrderDate, 112)))) ASC, POHeader.PONum desc
))
2

How are you joining your main data tables?

hdcase
podetail
poheader

might be better to have a small, focused test query to start things off.

1 Like
3

@Banderson just did a great presentation on this type of thing at Insights :slight_smile:

4

DA-AnthonyTest2.baq (71.7 KB)

Here is how it is setup.

image
image

5

You could just create a subquery of HDCase/PODetail/POHead to get the Part and date information you are looking for.

Guessing you will want to add a criteria like when HDCase Date is less than POHead.OrderDate or something like that. I’m not clear on when you say closest PO date, do you mean after or before the hdcase date?

6

When you say “It’s not working properly” what do you mean by that? Your field for the difference of the date is odd, you should be able to just do a datediff() for the differences in the date. It returns and int, so you can take the ABS of that. But theoretically, that should get you the same as what you are getting there.

The other the joins on your query are a bit odd. See the screen shot.

image

1 Like
7

I’m honestly not sure why I couldn’t get the DATEDIFF to work before, I was probably using the syntax wrong.

So my calculated field would look more like this?

(ROW_NUMBER() OVER (
    PARTITION BY HDCase.HDCaseNum, PODetail.PartNum 
    ORDER BY ABS(DATEDIFF(day, HDCase.CreatedDate, POHeader.OrderDate)), POHeader.PONum DESC
))

Also, would the joins look better like this then?

image
image676×521 18.8 KB

And is this fine where I connect the sub to my main query with HDCaseNum?
image

8

You want left joins. Outer join will show all on both sides of the table. You don’t want that. So on both places that I can see them in your screen shots, change them to left join.

You still haven’t said how it’s not working yet. So I don’t really know what you are trying to fix.

9

My bad I was trying to find the differences between this query and the old summary query. Not sure if this is the only issue, but it is ranking different part numbers within the same part 1,2,3,4 instead of 1,1,1,1 in the same case.
image

I’m assuming this just has something to do with my calculated row number being messed up but I am not very good at the SQL side of things. Essentially every part should for sure have a row number 1. If there is multiple PO’s…it should grab the closest date to the creation date of the case, make it 1, and then rank every PO after that 2,3,4 etc. This should be per-case, so if a part number shows up over multiple cases it should start the rank at 1 again.

Also, not sure if this is still part of the calculation or my filter, but it shouldn’t matter if there is a PO or not, if there is no PO then just still give it a rank of 1.

10

Throw in the case number in your results, so you can see where the partition should be happening.

Also, were do you have the filter display only 1’s. I would expect to see a filter on this table/sub-query, but there isn’t one.

image

11

The filter is in the linkage between HDCase1 and Sub. It just filters to Sub_RwNm = 1.

I do have the case numbers in the results, I just couldn’t show all the columns in that screenshot. Maybe this can show better what I mean?

image
image840×639 10.6 KB

And maybe it has something to do with the fact that there’s no PO for that part? Even still it should give them all a 1.

12

If that were the case, then there would be an fx in here. Double check, or better yet, put the filter on the table because that is better anyways. That outter join is going to return all rows, and if they don’t match… it’s just going to return the rows not joined.

image

13

I had removed it to be able to diagnose and show the other RwNm’s to see what was wrong. But thanks for the advice also, I moved it to table criteria…

image

14

And when I add the criteria back it filters down to 1 like it is supposed too…the only issue is that it cuts my results short and doesn’t show my other part numbers in cases, like case 485.

image
image776×116 3.48 KB

15

Double check your calculated field. Make sure that the field that you have in the calculated field for the partitioning is the same field that your are displaying. They should be flipping back on 1 every time the part number changes. It’s obviously not doing that, so you have something not right in your field.

16

You’re right, I think I needed to partition by PartCost.PartNum and no PODetail or HDCase.

I’m at 1326 results now and that’s a lot closer to my original BAQ than before. I think the 4 number difference in results is just due to the fact that we have some cases with duplicate part entries and I cannot think of a way to make those stay in. However, I do not think it becomes that big of an issue.