Hello all,
I'm attempting to write a function that inserts a date ("End") based on a serial number recorded in the table. The function looks for a previous instance of SERIAL_NUMBER_RECEIVED and if none is found, populates the "End" field with Today's date. If a previous instance of SERIAL_NUMBER_RECEIVED is found, then "End" is populated with a date that is one day prior to the previous instance's Receive Date.
In my dataset there is a single instance of a particular SERIAL_NUMBER_RECEIVED value, so I would expect to see the "End" field populated with today's date. However, when I look at the data in a straight table after the load, I see two values for "End". One populated with today's date and one populated with the date prior to the Recieve date. Can anyone explain what I'm doing wrong?
Script Code:
[ReturnsTemp]:
LOAD
ITEM_NUMBER,
CUSTOMER_NUMBER,
CUSTOMER_NAME,
ORDER_TYPE,
ORDER_NUMBER,
ORDERED_DATE,
DESCRIPTION,
LINE_TYPE,
LINE_STATUS,
RETURN_REASON_CODE,
SERIAL_NUMBER_RECEIVED,
ORDER_PACK_NOTES,
ORDER_SHIP_NOTES,
LINE_PACK_NOTES,
LINE_SHIP_NOTES,
LINE_NUMBER,
LINE_CATEGORY_CODE,
DATE_RECEIVED,
FROM [lib://SharedFolder_Quality_QVD/Returns_AVN_G1.QVD]
(qvd);
[Returns]:
LOAD
ITEM_NUMBER,
CUSTOMER_NUMBER,
CUSTOMER_NAME,
ORDER_TYPE,
ORDER_NUMBER,
ORDERED_DATE,
DESCRIPTION,
LINE_TYPE,
LINE_STATUS,
RETURN_REASON_CODE,
SERIAL_NUMBER_RECEIVED as Returned_SN,
ORDER_PACK_NOTES,
ORDER_SHIP_NOTES,
LINE_PACK_NOTES,
LINE_SHIP_NOTES,
LINE_NUMBER,
LINE_CATEGORY_CODE,
DATE_RECEIVED as [Receive Date],
Date(If(SERIAL_NUMBER_RECEIVED = Previous(SERIAL_NUMBER_RECEIVED), RangeSum(Previous(DATE_RECEIVED), -1), Today())) as End //CR Service Completion Date should be between start and end date
Resident ReturnsTemp
Order By SERIAL_NUMBER_RECEIVED, DATE_RECEIVED desc;
DROP Table ReturnsTemp;
