Rob checks this report in spotfire

Sales Dollars 2020-3-6-rf


Why this issue occurs:


This happens because every so often, Unix happens to place a duplicate line item on a single invoice - aka - there are two different items in line position 2, and the auto incrementing process did not adjust the second 2 to be a 3. This then prevents the ETL process from reporting bad data on any report, and does not report up-to-date information.


How to fix:


First, you want to use the two scripts attached below. You want to run the Duplicates by UnixID.sql file first, this will return a small data frame that has our needed information. (See image below)

-img1

 


The important data listed in the image above is in the highlighted cell above 22128604139633.


After copying the UnixID, open the secondary script below named Verify and Update Line Number.sql. 

With this open, follow these steps:

  1. Paste the UnixID into the position that literally states 'where UnixID = ..'
    1. Only change the numeric portion of this text
  2. Highlight the area from SELECT TOP down to the first semicolon.
    1. The first semicolon comes after the line "order by [LineNo];"
  3. Execute this portion then by selecting the execute button at the top
  4. This will return a small dataframe that shows the entire order with the duplicated line item number (see image below)
    1. Img2

       

  5. After executing this script, you will need to get these bits of information from the small dataframe in the image above
    1. LineNo of the duplicated item
    2. ItemNo of the duplicated item
    3. In the example above, the values above will be LineNo = 2, and ItemNo = 93560
  6. We will want to go back to the script, and update the information. We will start in the script where it says update
    1. from there, you must change the following values
      1. UnixID = 22128604139633
      2. ItemNo = 2
      3. [LineNo] = 93560;
    2. in this example. the values listed above can all be found in the img2
  7. Now we must change the line where it says set. Change this to the next highest value on the order. So, in the example above the highest value in the LineNo column is 3, so we will change our value to 4.
  8. After this, we can execute the script. We will want to hightlight the section we have changed, so starting at the word updateto the semicolon. If this is run properly, you should see something similar to the img3
    1. img3

       

  9. If you want to double check your work, you can select the top half of this script and run it again. This will return a very similar dataframe as shown in img2, except the values circled in red in img2 should now be different.




Final Steps

  1. Change Database to ETL Database, and execute the stored procedure - etl db - dbo.sp_LOAD_Inventory_Export
    1. You only want to execute from the first MERGE line until the final semicolon.



To confirm all of the steps above have been followed correctly, open the spotfire application and check the report Sales Dollars 2020-3-6-rf.  If you do not see the current days date, then you may have missed a step. If you do see todays today, the information has been updated correctly.